How to Lookup Values to Left?
There is no argument that VLOOKUP is a beautiful & useful formula. But it suffers from one nagging limitation. It cannot go left.
Let me explain, Imagine you have data like below. Now, if you want to find-out who made $2,133 in sales, there is no way VLOOKUP can come to rescue. This is because, once you search a list using VLOOKUP, you can only return corresponding items from the column at right, not at left.
One easy fix would be move the sales data to the left of person name. But this is an annoying fix, because, god knows you may want to lookup based on profit values or something else in future. A better alternative is,…
.., to use a formula combination called INDEX + MATCH (or OFFSET + MATCH would work too).
The basic syntax of this combination is like this:
=INDEX(column with data you want,MATCH(value you are looking for, column which contains this data,0)). So, for eg:
=INDEX($B$5:$B$17,MATCH(1088,$D$5:$D$17,0)) would find the position of 1088 in list D5:D17 and return corresponding element from B5:B17 (ie the value from left). See more examples below.
Go ahead and download the file. It also has some homework for you to practice this formula trick.
Special Thanks to
Sign-up for our FREE Excel tips newsletter:
Here is a smart way to become awesome in Excel. Just signup for my FREE Excel tips newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:
Your email address is safe with us. Our policies
Leave a Reply
|Mix VLOOKUP with Data Validation for some magic! [VLOOKUP Week]||How to Look up Based on Multiple Conditions|