VLOOKUP problem

[Jon]

I'm trying to use VLOOKUP to find values on sheet1, looking up values from
sheet2, so basically:

A2 to Axx: =VLOOKUP(D2,Sheet2!A1:B154938,1,FALSE)
B2 to Bxx: =IF(A2=C2,"Found","") - Display "Found" if vlookup (column A) is
successful; blank if none
C2 - the value i want to look up e.g. 000018901

The problem is that since when the values are not found VLOOKUP returns
'#N/A', the values are returned as:

'Not found' when the IF statemetn (B2) is true; whilst it should be 'Found'
'#N/A' for other values.

Is this a bug?

 

[Roger Govier]

Hi Jon

I don't think you can have given us exactly what you have in your formula,
as there is now way that "Not Found" could be returned.
Also, If the value you want to lookup is in C2, then it is C2 that should
appear in your Vlookup formula, not D2.

I think the following will do what you want

In A2
VLOOKUP(C2,Sheet2!A1:B154938,1,FALSE)
In B2
=IF(ISNA(A2),"Not Found",IF(A2=C2,"Found",""))
--
Regards
Roger Govier

I'm trying to use VLOOKUP to find values on sheet1, looking up values from
sheet2, so basically:

A2 to Axx: =VLOOKUP(D2,Sheet2!A1:B154938,1,FALSE)
B2 to Bxx: =IF(A2=C2,"Found","") - Display "Found" if vlookup (column A)
is
successful; blank if none
C2 - the value i want to look up e.g. 000018901

The problem is that since when the values are not found VLOOKUP returns
'#N/A', the values are returned as:

'Not found' when the IF statemetn (B2) is true; whilst it should be
'Found'
'#N/A' for other values.

Is this a bug?






__________ Information from ESET Smart Security, version of virus
signature database 4741 (20100104) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4741 (20100104) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Jon]

Having gone over my formula, I think the formula for VLOOKUP is fine
otherwise I wouldn't be seeing '#N/A' for the VLOOKUP and 'Not found' -
instead of 'found' in B2. I think the problem might be that the IF statement
is not matching the values of the cells in An and Dn,

So for example:

Sheet1!A1: =VLOOKUP(D2,Sheet2!A1:B154938,1,FALSE)
This returns #N/A when there is no match and the number when there is a match.

Sheet!B1: =IF(ISNA(A2),"Not Found",IF(A2=C2,"Found","Not found"))
Does not validate the part of the IF statement ...IF(A2=C2,"Found","Not
found" to "Found" even when visually the same value is seen in An and Dn.
I tried updating the format of both cells to "Text", unsuccessfully.

 

[Roger Govier]

Hi Jon

It works fine for me.
I did assume that the data was Text, because of all the leading zero's, so I
entered it as text (by using a preceding single quote) in both cell
Sheet1!C2 and in column A of Sheet2

--
Regards
Roger Govier

Having gone over my formula, I think the formula for VLOOKUP is fine
otherwise I wouldn't be seeing '#N/A' for the VLOOKUP and 'Not found' -
instead of 'found' in B2. I think the problem might be that the IF
statement
is not matching the values of the cells in An and Dn,

So for example:

Sheet1!A1: =VLOOKUP(D2,Sheet2!A1:B154938,1,FALSE)
This returns #N/A when there is no match and the number when there is a
match.

Sheet!B1: =IF(ISNA(A2),"Not Found",IF(A2=C2,"Found","Not found"))
Does not validate the part of the IF statement ...IF(A2=C2,"Found","Not
found" to "Found" even when visually the same value is seen in An and Dn.
I tried updating the format of both cells to "Text", unsuccessfully.



__________ Information from ESET Smart Security, version of virus
signature database 4743 (20100104) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4743 (20100104) __________

The message was checked by ESET Smart Security.

http://www.eset.com