Vlookup problem

  • Thread starter Thread starter Jim Berglund
  • Start date Start date
J

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
 
'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
 
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
 
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.
 
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")
 
Dave Peterson said:
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")
Click to expand...

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")
Click to expand...

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.
 
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.
 
Back
Top