Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem
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 "Jon" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem
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" wrote: 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 "Jon" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP problem
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 "Jon" wrote in message ... 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" wrote: 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 "Jon" wrote in message ... 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 . __________ 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP problem | Excel Discussion (Misc queries) | |||
Vlookup problem | Excel Worksheet Functions | |||
vlookup problem | Excel Worksheet Functions | |||
VLOOKUP problem with using a - | Excel Worksheet Functions | |||
vlookup problem | Excel Discussion (Misc queries) |