Forum Discussion
Solved
Excel Vlookup Error
Hey, I am using excel 2007, and having a problem while performing Vlookup in my demo data,
I am trying to find ProductId via ModelNumber, And when I am applying Vlookup to that value means on Model Number I didn't get the Product Id even though it exists in the table array. Please solve my query I am attaching Screenshot here.
=INDEX($B$2:$B$8,MATCH(E2,$C$2:$C$8,0))You can do this with INDEX and MATCH.
=VLOOKUP(E2,CHOOSE({1,2},$C$2:$C$8,$B$2:$B$8),2,0)This is the alternative with VLOOKUP. Enter this formula with ctrl+shift+enter if you don't work with Office365 or 2021.
If you work with Office365 or 2021 you can use XLOOKUP.
6 Replies
=INDEX($B$2:$B$8,MATCH(E2,$C$2:$C$8,0))You can do this with INDEX and MATCH.
=VLOOKUP(E2,CHOOSE({1,2},$C$2:$C$8,$B$2:$B$8),2,0)This is the alternative with VLOOKUP. Enter this formula with ctrl+shift+enter if you don't work with Office365 or 2021.
If you work with Office365 or 2021 you can use XLOOKUP.
OliverScheurich thanks allotted it work for me. Just explain to me why we need to write choose function?
VLOOKUP (without another formula) only returns values from a range to the right. For example you can look up a value in column B and return a value from column C or D or E... This is because columns C, D and E are on the right side of column B.
In your example the return column B is on the left side of the lookup column C. VLOOKUP without another formula can't do this. However it's possible to combine VLOOKUP and CHOOSE. With CHOOSE it's possible to define the lookup range and the return range.
Generally if the return range is on the left of the search range INDEX and MATCH are applied or XLOOKUP when working with Office365 or 2021.
- Hello 🙂
The vlookup fonction use the first column by default to look for your modelNo.
You can try to change the order of your column for better results.
For more information about vlookup or alternative: https://support.microsoft.com/en-us/office/look-up-values-with-vlookup-index-or-match-68297403-7c3c-4150-9e3c-4d348188976b - Formula I used in F2 Cell :- =VLOOKUP(E2,A1:C18,1,FALSE)
