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
Introducing our Online Power BI Class:
Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.Click here to know more and join us.
Leave a Reply
|Mix VLOOKUP with Data Validation for some magic! [VLOOKUP Week]||How to Look up Based on Multiple Conditions|