We all know that VLOOKUP works to the right (of the column with the lookup value). But what if we want to the ability to lookup a value to
One solution is to use INDEX and MATCH together. Well, there is another, more elegant solution. And Szilvia Juhasz (aka XSzil) is here to demonstrate its use to us.
Look at the data in the image below. Suppose that you wanted to loop up the captain’s name given the team name. You will have to lookup to the left. If you also wanted to find the best week of that team using just the name, again you will have to lookup to the left.
The VLOOKUP to the left is actually very easy to implement. Just use the following syntax:
This function will first find the ‘lookup_value’ in the ‘lookup_column’. Upon finding it, it will return the corresponding value from ‘retrieve_column’. The ‘FALSE’ stands for an exact match, just like when using ordinary VLOOKUP function.
It’s easy to see now how one can use it to VLOOKUP to the left. Let’s say I have a team’s name and I want to lookup its captain’s name. Well, I will go through the following steps:
And, I am done. I have just used VLOOKUP to the left. Interesting, right?
Notice the following two things:
So, my ‘retrieve_column’ could be on another sheet and not even in the corresponding place as my ‘lookup_column’. Now this is amazing!
Can’t wait to try this on your own? Go ahead by all means.
But, make sure to share this obscure little trick with other Excel experts.
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.