Lookup value - #N/A result

Question

I am using =IF(VLOOKUP(A2;$B$2:$B$11;B2;FALSE); TRUE; FALSE) to lookup a value in the column A.

enter image description here

As you can see my formula does not seem valid. Any suggestions why this is the case?

I appreciate your answer!

UPDATE

I am currently getting with =ISNUMBER(VLOOKUP(A2;$B$2:$B$11;1;FALSE)) only FALSE values. However I want to see True if there is a match:

enter image description here

Update

Using =Not(ISNA(VLOOKUP(A2;$B$2:$B$11;1;FALSE))) and =ISNUMBER(MATCH(A2, $B$2:$B$11, 0)) gives me the same result:

enter image description here

use `VLOOKUP(A2;$B$2:$B$11;1;FALSE)` - note, 3th argument is equal to `1` - column number in array `$B$2:$B$11` from what you get result — Dmitry Pavliv, Feb 15 '14 at 12:01
`why are you using 1 for the col_index_num` because `$B$2:$B$11` has only one column — Dmitry Pavliv, Feb 15 '14 at 12:02
@simoco actually I just had to fill this field and therefore I used "something". Btw I still get `#VALUE` with `=IF(VLOOKUP(A2;$B$2:$B$11;1;FALSE); TRUE; FALSE)`? — user2051347, Feb 15 '14 at 12:03
try `=NOT(ISNA(VLOOKUP(A2;$B$2:$B$11;1;FALSE)))` - it will give you desired result — Dmitry Pavliv, Feb 15 '14 at 12:05
it's because VLOOKUP returns sth or `#N/A` (when nothing found) and `If(#N/A;True;Flase)` returns `#Value`, because `If` expected boolean expression in first parametr — Dmitry Pavliv, Feb 15 '14 at 12:06
@simoco Thx a lot! However I receive different results! Please also see my update! — user2051347, Feb 15 '14 at 12:09
my fault:) use `=Not(ISNA(VLOOKUP(A2;$B$2:$B$11;1;FALSE)))` or formula, suggested Jerry:) — Dmitry Pavliv, Feb 15 '14 at 12:10

Jerry · Accepted Answer · 2014-02-15T12:17:48.887

2

VLOOKUP takes the following arguments:

=VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup])

lookup_value is what will be looked for in the first column of table_array.

table_array is the table in which the lookup_value and the value to be returned are.

col_index_num is the number indicating the nth column within table_array from which the value is to be returned from.

[range_lookup] (defaults to true) indicates the type of lookup, true being approximate and false being exact.

As such, if you want to find if A2 is in table $B$2:$B$11, you need to use a col_index_num of 1.

A simpler formula however would be with MATCH:

=ISNUMBER(MATCH(A2; $B$2:$B$11; 0))

MATCH returns the relative row number in which the value A2 is found. If there is a match, you get a number and hence ISNUMBER returns TRUE.

If you want to check if a value from column B exists in column A, then you have it reversed in the formula you used. Turning it around gives:

=ISNUMBER(MATCH(B2; $A$2:$A$11; 0))

edited Feb 15 '14 at 12:17

answered Feb 15 '14 at 12:07

Jerry

Thx for your answer! Please have a look at my update, because I do not get the derised results... – user2051347 Feb 15 '14 at 12:10
@user2051347 Are you sure you used the formula I suggested? :) – Jerry Feb 15 '14 at 12:11
Yep I am using `exactly` `=ISNUMBER(MATCH(A2, $B$2:$B$11, 0))` However I receive in the first col `FALSE` and then all over `TRUE's`. – user2051347 Feb 15 '14 at 12:13
1

@user2051347 I just checked your 'Expected Result' column and there is a `b` and a `d` in column B there. – Jerry Feb 15 '14 at 12:16
1

@user2051347 OR: are you checking if a value in column B exists in column A? That's a different thing. – Jerry Feb 15 '14 at 12:16

score 1 · Answer 2 · answered Feb 15 '14 at 15:19

1

You could also use COUNTIF, i.e. this formula in C2 copied down

=COUNTIF(A$2:A$11,B2)>0

answered Feb 15 '14 at 15:19

barry houdini

2 Answers2