Vlookup problem

[Jim Berglund]

I have two worksheets with some matching data. I'm trying to use the following formula to determine if the values in column A on WS1 exist in Column A on WS2.

=IF(VLOOKUP(A2,'Equipment Data'!$A$2:'Equipment Data'!$B$9000,1),"T","F")
I am getting #N/A and #VALUE responses.

What have I done wrong?

Thanks

Jim Berglund

 

[Jim Berglund]

'Sorry, the formula used was actually

=IF(VLOOKUP(A2,'Equipment Data'!$A$2:!$B$9000,1),"T","F")
I have two worksheets with some matching data. I'm trying to use the following formula to determine if the values in column A on WS1 exist in Column A on WS2.

=IF(VLOOKUP(A2,'Equipment Data'!$A$2:!$B$9000,1),"T","F")
I am getting #N/A and #VALUE responses.

What have I done wrong?

Thanks

Jim Berglund

 

[Arvi Laanemets]

Hi

Maybe
=IF(ISERROR(VLOOKUP(A2,'Equipment Data'!$A$2:!$B$9000,1,0)),"F","T")

(4th parameter set to 0 forces exact match to be searched for)


Arvi Laanemets


'Sorry, the formula used was actually

=IF(VLOOKUP(A2,'Equipment Data'!$A$2:!$B$9000,1),"T","F")
I have two worksheets with some matching data. I'm trying to use the
following formula to determine if the values in column A on WS1 exist in
Column A on WS2.

=IF(VLOOKUP(A2,'Equipment Data'!$A$2:!$B$9000,1),"T","F")
I am getting #N/A and #VALUE responses.

What have I done wrong?

Thanks

Jim Berglund

 

[NewsMan]

why don't you try something like this:

=IF(ISERROR(VLOOKUP(A1,'Equipment Data'!$A$2:!$B$9000,1,FALSE)),"",A1)

This will return a blank cell if the numbers don't match or the number
if they do. You can enter this in cell B1 of WS1 and then autofill down.

 

[Dave Peterson]

Since you're only checking for existence, you could use =match(), too.

=if(isnumber(match(a2,'equipment data'!$a$2:$b$9000,0)),"T","F")

or even...

=if(countif('equipment data'!$a$2:$b$9000,a2)>0,"t","f")

 

[Harlan Grove]

Since you're only checking for existence, you could use =match(), too.

=if(isnumber(match(a2,'equipment data'!$a$2:$b$9000,0)),"T","F")

Can't use MATCH against nontrivial 2D ranges. This should be

=IF(ISNUMBER(MATCH(A2,'equipment data'!$A$2:$A$9000,0)),"T","F")

or even...

=if(countif('equipment data'!$a$2:$b$9000,a2)>0,"t","f")

COUNTIF is better since one COUNTIF call does the work of two other
functions, ISNUMBER and MATCH, but also should only look through col A since
no match for A2 in col A but a match in col B would be a false match.

 

[Dave Peterson]

Oops, I didn't notice that it was two columns in that range.

But if it were only one column, I think I prefer the =isnumber(match()) better
than =countif(). With lots of data (whatever that means), =countif() seems to
be slower.

 

[Jim Berglund]

Thanks to all of you. I have it working, now.

Jim