» Altering the results returned by VLOOKUP.
CATEGORY - Lookup Formulas
VERSION - All Microsoft Excel Versions
Column B contains values to look up in List1 (Column A).
How could we alter the results returned by the VLOOKUP function so that they will include the original value looked up and a ""Found""/""Not Found"" indication?
Solution:
Use the ISNA and VLOOKUP functions in an IF statement, as follows:
=IF(ISNA(VLOOKUP(B2,$A$2:$A$7,1,FALSE)),B2&"" Not Found"",VLOOKUP(B2,$A$2:$A$7,1,FALSE)&"" Found"")
List1_____Values to look______Result
Blue______Pink________________Pink Not Found
Red_______Blue________________Blue Found
Green_____White_______________White Not Found
Black_____Purple______________Purple Not Found
Yellow____Red_________________Red Found
Orange
Book Store:
Reply: fishergomz
Alan
Hi fishergomz,
[QUOTE=fishergomz]Why the 2nd vlookup? Is the following enough?
=IF(ISNA(VLOOKUP(B2,$A$1:$A$6,1,FALSE)),B2&" not found",B2&" found")[/QUOTE]You could do that, but instead of returning the corresponding value to B2, it would, of course, return B2.
Depends on what you want.
Alan.
I need to learn from basic of excel macros with example
Gunasekaran Mani
Please help me to get notes for that,and you can mail me at forestguy@rediffmail.com
adapt VlOOKUP
mathieu borms
hey everyone,
The last variable you have to put in is True or False. When you put in True you get the next smaller value. But I want to get the next higher value. is that possible?
thanx
macros and vb examples
Kailash Alchetti
Hi,
I need to learn from basic of excel macros with examples
and also vb codes written from basic with examples.
Mail me if you can to = a.kailash@rediffmail.com
Thanks,
vlookup
Theresa
When setting up vlookup, and using "false" - I would like to receive an output of "blank" rather than "0". I expect the "0" output is due to the type of data. But I would prefer to show blank. What do I need to change in the calculation? Thanks
vlookup with formatting
Greg Luby
I am trying to use vlookup and having it return a value in a colored cell.
vlookup with formatting
Greg Luby
I am trying to use vlookup and having it return a value in a colored cell.
=IF(ISNA(VLOOKUP(B2,$A$1:$A$6,1,FALSE)),B2&" not found",B2&" found")