I am having an issue with the data in a VLOOKUP formula. I have done a fair amount of research online and have spent several hours trying to figure out the problem with the data integrity between the key columns. I haven't been able to figure it out, so I thought I would ask this esteemed group. The worksheet is attached.
NBA.xlsx
Observation:
If I remove 2 rows of key data above the match, the VLOOKUP does return the correct value.
A - Trying to cleanse the data from B for the VLOOKUP fucntion.
B, C, D - Extrated Data from column I
F, G - Test VLOOKUP function
I - Original Source Data
Last edited by mowine; 01-10-2011 at 05:25 PM.
mowine,
In worksheet Doesn't Find Match, in cell H1, enter the following formula, and copy down:
=IF(ISNA(INDEX(C:C,MATCH(F1,A:A,0),1)),"",INDEX(C:C,MATCH(F1,A:A,0),1))
Have a great day,
Stan
stanleydgromjr
Windows Vista Business, Excel 2003 and 2007
If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
Hello,
If you use Vlookup without the fourth parameter, it expects the list to be sorted, which it is not.
Use False as the fourth parameter
=VLOOKUP(F1,$A$1:$D$30,3,FALSE)
and all will be good.
@ Stan, in Excel 2007 and older, you could also use IFERROR
=IFERROR(INDEX(C:C,MATCH(F1,A:A,0),1),"")
teylyn,
Missed the fact that the OP had Excel 2010.
Have a great day,
Stan
stanleydgromjr
Windows Vista Business, Excel 2003 and 2007
If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks