SubscribeHow to Lookup Values to Left?
Situation
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.
Data:
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,…
Solution
.., 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.
Examples:
Sample File
Download Example File – Make VLOOKUP go Left
Go ahead and download the file. It also has some homework for you to practice this formula trick.
Special Thanks to
Prem, Rohit1409, John, Godzilla, Chris Byham, judgepax – Please click on their names to learn even more.
Similar Tips
| ||||
|
| ||||
|
Leave a Reply
 |
Mix VLOOKUP with Data Validation for some magic! [VLOOKUP Week] | How to Look up Based on Multiple Conditions |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
59 Responses to “How to Lookup Values to Left?”
A good example of how easy, when you know index + match, how how to lookup anything anywhere.
Homework:-
1. =INDEX($B$5:$B$17,MATCH(LARGE($D$5:$D$17,2),$D$5:$D$17,0),1,1)
2. =INDEX($F$5:$F$17,MATCH(LARGE($D$5:$D$17,2),$D$5:$D$17,0),1,1)
3. =(LARGE($D$5:$D$17,1)/INDEX($C$5:$C$17,MATCH(LARGE($D$5:$D$17,1),$D$5:$D$17,0),1,1)) – (SMALL($D$5:$D$17,1)/INDEX($C$5:$C$17,MATCH(SMALL($D$5:$D$17,1),$D$5:$D$17,0),1,1))
Dear I want left site value by vlookup,
please share me,
Thankx
satendra kumar
Sir, for example #1 you made, what if there are more than 1 person who has sales of 1088? What will be the result?
1. =INDEX($B$5:$B$17,MATCH(LARGE(D5:D17,2),$D$5:$D$17,0))
2. =INDEX($B$5:$B$17,MATCH(MAX(D5:D17),$D$5:$D$17,0))
Chandoo…good examples in your workbook download. Worth noting that we can use full column references in this case, which cuts down on some of the characters:
1. Which person made sales = 1088
=INDEX(B:B,MATCH(1088,D:D,0))
2. Who made maximum sales?
=INDEX(B:B,MATCH(MAX(D:D),D:D))
3. Who sold to minimum number of customers?
=INDEX(B:B,MATCH(MIN(C:C),C:C,0))
4. What is sale per customer for the person who has lowest profit ratio?
=INDEX(D:D,MATCH(MIN(F:F),F:F,0))/INDEX(C:C,MATCH(MIN(F:F),F:F,0))
…which is really the same as giving them named ranges. And if you give them explicit named ranges, then the 4th one can be done like this:
=SUMPRODUCT((MIN(Profit_Ration)=Profit_Ration)*Net_Sales/No._Customers)
Hello Jeff….So EXCELLENT! Ur formulas say it all…& with CLARITY.
But i have a Q.
Assuming i have a data set of 100 sales reps & i want to know-
Who made among the top 10 for Sales??
ThanX
Hi Chandoo, Just learnt a new trick (daddylonglegs on Excel Forum Site) to use Vlookup to look up columns to the left and thought you would be interested as have not seen it on your site. You can use Choose() to look up columns to the left i.e. in you example 1 above:
=VLOOKUP(1088,CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17),2,0) = “John”
I am sure this has a great deal of other applications
regards, Mike
Hi Mike,
I have tried your formula
Its working and I am really happy…
thks a lot..
Dear Chandoo, i want to match particular name ( Sheet 1 ) to name ( sheet 2) and after matching it has paste next column value( Sheet 2 ) to next column of name ( 1 sheet )
Pls clear me this, how can i do this.
What if the value in the cell is a date, and I want to match just the day and month (exclude the year)? For example, today is 12/9/10 and every year on 12/9 a donation of $100 is made to St Judes children hospital. Would the match function be able to compare today’s month and day to the dates within a table of annual expenses? (without needing to separate the month and day from the date into two columns)
Hi hellomoto:
I think this may work:
=INDEX(B2:C9,MATCH(F3&G3,(DAY(B2:B9)&MONTH(B2:B9)),0),2)
enter with ctrl+alt+delete
Where:
B2:C9 is the array where the data is
F3&G3 are the cells day and month that you want data for
Hope it works
Mike,
What did you mean by enter with ctrl+alt+delete?
I tried that formula but got the #value error. BTW, using excel 2003.
Hi,
sorry it should have been “ctrl+alt+enter” wasn’t thinking when i replied
Should work for 2003. Pity I can’t upload an example.
The formuale needs to be converted to array type formulae. This is done by entering the formulae as normal and then place your cursor in the formulae bar and entering “ctrl+alt+enter” all at the same time.
Mike
It should be CTRL+SHIFT+ENTER
Mike and Chandoo, in the formula is F3&G3. Please explain the ‘&’. Is it short hand for AND? I searched Excel help files to get the meaning of &, but no hits.
BTW, I am using 2007 now. In a few months, I will get 2010
Thx
& is used to concatenate 2 values. So if F3 has “a” and G3 has “b”, F3&G3 will be “ab”.
Thought you were on vacation now
Thanks for the quick repsonse. I’ll look up concatenate to further understand what’s going on. Ultimately, I am just looking for a simple way (via formula) to look up data by month and day. I am working on a little personal budget worksheet. Because Excel assigns unique numbers to dates, I am using two columns for the date. One column formatted to show month and second to show day. Then using Vlookup and IF to pull a cell value from a table of due dates (day and month in 2 separate columns). To keep this short, I want use a single column for dates. I posted this question in the forum, but have not found a good solution yet.
Thx in advance, Happy holidays!
Yes, I’ve calculated the last homework by the sumproduct as below:
=MAX(D5:D17)/(SUMPRODUCT(((D5:D17)=(MAX(D5:D17)))+0,C5:C17))-MIN(D5:D17)/(SUMPRODUCT(((D5:D17)=(MIN(D5:D17)))+0,C5:C17))
Looks a little bit complicated, but the same result as by using index. Seems sumproduct is powerful, thanks for Chandoo’s another post regarding sumproduct.
HI CHANDOO,
IT’S REALY VERY GOOD AND HOW CAN I GET TUTORIAL FOR THE SAME SO THAT I CHOULD LEARN THIS TYPE OF THINGS, I M VERY HAPPY TO SEE THIS TYPE OF REPORTS , WHICH COULD BE BENEFICIAL FOR EVERY BODY.
Hi,
have the same question earlier asked by Arman, what if the value looking for is present in two cells, in other words, what if there are more than 1 person who has sales of 1088? How to show all results?
[...] This is a bit tricky, but easy once you figure out the formula. We will use INDEX+MATCH. [...]
Just went through the homework and wanted to post it, @ Jason, i made a couple corrections to your formulas.
Homework:
1. =INDEX(B5:B17,MATCH(LARGE(D5:D17,2),$D$5:$D$17,0))
2. =RANK(INDEX(F5:F17,MATCH(LARGE(D5:D17,2),D5:D17,0)),F5:F17)
3. =LARGE(D5:D17,1)/INDEX(C5:C17,MATCH(LARGE(D5:D17,1),D5:D17,0))-(SMALL(D5:D17,1)/INDEX(C5:C17,MATCH(SMALL(D5:D17,1),D5:D17,0)))
[...] Using this series name, we can fetch the position of the series out of 4 with MATCH formula. Once you know the position, You can fetch corresponding values using INDEX() [...]
[...] http://chandoo.org/wp/2010/11/02/how-to-lookup-values-to-left/ [...]
Hi Chandoo,
I am novice in Excel. Hope you can guide me to correct excel formula. I have a spreadsheet “1″ with data for different countries as follows:
A B
1 Iran 150
2 Bolivia 100
3 India 500
4 Malaysia 1000
Now I want arrange data in a “separate spreadsheet” (lets say: 2) with countries sorted in my own order (considering there are ). Can you let me know what formula works for the same. Thank you.
@GSS
I would add a helper column and use the Rank function to add a Rank value
Then you can use a Vlookup to retrieve the values that match the ranks 1..x each in separate rows
I have a budget, with consultants’ names, wether or not they are inhouse (yes/no), then 12 columns for time spend in every month. In a Data sheet (with among other information) their hourly rate, which varies per month. I need to calculate in sheet 1, as a total “below” the sum of hours per month, the cost of the External consultants and the cost of my inhouse consultants as seperate subtotals. So far I have :{=SUM(IF($H$52:$H$74=”no”,L$52:L$74*(VLOOKUP($E87:$E108,’Data Sheet’!$B$3:$R$36,HLOOKUP(‘Data Sheet’!G$1,’Consult Check’!L$1:W$1,1,0)-39,0))))}; column H defines Inhouse Yes/No; column L = month 1′s hours; column E = Consultant’s name; Data sheet row 1 is a number of the month as this is an ongoing budget for more than one year, matching it with the number in my current sheet, to ‘extract’ the correct month’s hourly cost. Unfortunately, the formula uses the first consultant’s cost and not the specific consultant’s cost. Other than “manually” selecting each consultant’s hours and the corresponding rate, what formula can I use?
to add: there are several projects and several consultants working on them, some off course on more than one project per month. As the ‘sales manager/director’ have to complete this section, I want to ‘bullet proof’ the formulas so that in case of any changes made, different consultant selected than ‘version 1′, I do not have to ‘redo’ the specific formulas to ensure the costing is correct. I would also prefer not to have to add an additional sheet to do these calculations on.
Hi all, I’m wondering if one of you can help me with a similar type of formula I can’t seem to get to work. I’m working between 2 sheets and need to find a value from sheet 1, column a that will be inserted into sheet 2, column b. it is not numeric, it’s a text/general field. the qualifier for it is that i need it to be when it is between a date range, columns c and e on sheet 2, and equals values in fields j and k.
what i would be looking for is to get the value from A:A from sheet one to populate into sheet 2 column g2 when the values of sheet 2 columns j2 and k2 equal the values in sheet 1 D? and E?, and the date in sheet 1 E:E is equal to or between fields c2 and e2 on sheet 2.
thanks in advance
[...] used: INDEX | OFFSET| INDIRECT | Array Formulas | Using Date & Time in Excel Spread some love,It [...]
Please note that the index and match is beautiful, but when it comes to sorting, that formula will choke as its reference points will be all messed up…
Chandoo,
The below formula isn’t working if there are multiple sales people achieving the same net sales. Can you suggest a solution?
=INDEX($B$5:$B$17,MATCH(1088,$D$5:$D$17,0))
If you do VLOOKUP doesnt work either : VLOOKUP(1088,CHOOSE({1,2}, $B$5:$B$17,$D$5:$D$17),2,0) , it will only give one result if two 1088 are given
Can someone please help me and Sophia?
Ania
Hello,
Great site, lot’s of info. Did you, by any chnace, get the soltuion to this one? I want to return multiple values (in a list)…
I have a drop down menu in Column A of different subsidiaries and I would like to create a formula for if in Column A = Company A + Hours (Comun E) = Total
[...] 2010 Chandoo wrote a post about options to perform a VLookup to the left of the Reference [...]
Dear Chandoo
sample file for your reference http://speedy.sh/2tyra/lookup-across-spreadsheet.xlsx
Chandoo.org is one of the best Site, i am loving it ! i just happen to do rnd on the said formula and i found that not only we can do vlookup from left to right but also across spread sheet , awesome
thanks a ton for this awesome world dedicated to excel
Hi,
Can we get the answer to the questions asked in the workbook:
1. Who sold second highest?
2. What is the Profit Ratio rank of person who sold second highest?
3. What is difference in sale per customer between the highest selling & lowest selling sales persons?
With the formulas used as well.
Thanks.
Anurag Singh
Please disregard my earlier message just saw the answers in comments.
VLOOKUP(1088,CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17),2,0)
Can somebody please provide explanation for this formula provided by Mike which can used for VLOOKUP on left.
This is what I’ve found regarding
VLOOKUP(1088,CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17),2,0)
It can be simplified as follows:
VLOOKUP(value_a, CHOOSE({column_index_of_value_a,column_index_of_return_value},column_value1,column_value2),2,0)
So, in this case, you wanted to find value 1088 which is in $D$5:$D$17 (column index 2) and return the corresponding value in $B$5:$B$17 (column index 1)
Hope this helps
Also, you can look at it like this:
CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17)
CHOOSE function will create an array of $D$5:$D$17 (Net Sales) as the 1st column and $B$5:$B$17 (Sales Person) as the 2nd column.
So, basically VLOOKUP still searches from left to right.
Hi Chandoo.
I just want to ask how you make a list of names who for example failed in a particular exam. I have a list of 50 students in column A and their marks in column B. Some of them got the failing mark of 70%. How do make a list of these students who got failing marks in a separate worksheet. What formula will i use which would automatically determine and list the names on the basis of such criteria. Thanks!
Hi i have gone through the different solutions but failed to get the solution for my problem. I have a table of data having date name and salary. Each salary revision will be added to the table as a new row and i need to find the salary of a person on a particular date. The result should be the salary of the particular employee on the maximum date less than the particular date. I am attaching the data here. I appreciate for you kind help on this..
EffectiveDate
Name
Salary
01-Jan-2012
Jason
10000
01-Jan-2012
Robin
12500
01-Jan-2012
Paul
11000
01-Mar-2012
John
15000
01-Mar-2012
Paul
11500
01-Mar-2012
Robin
15000
01-Jun-2012
John
20000
01-Jun-2012
Paul
12500
01-Jun-2012
Jason
15000
@Rinto
Can you post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
Dear Rinto,
run a macro wherein the 3 rows get transposed in columns and rest of the criteria would be easy to find either by using autofilters or by running a formula.
I cannot attach a sample file in this blog. Hope this would work.
regards,
H.Prashanth
Hi Chandu,
I have a query regarding grouping in excel.. I have a list of values (range of value). I have grouped them based on the category 0 to 50, 50 to 100, 100 to 150, 150 to 200 and so on. It works perfectly. Only issues that I face is when I update the value which falls under these range does not reflect under them instead they stand out separate. So, what I do is I ungroup and regroup based on the above condition. This activity is done every time when there is a value change or updation in data.
Is there a formula which can be used show the count to map the condition like.. value between 50 to 100 etc
Thanks,
Rajesh
hi Chandoo,
I think we can use =lookup(A1,{0,51,100},{“Fail”,”Pass”,”Pass”}) also.
Dear Chandoo,
I had gone through your formulae. But I think its not working like vlookup. Whenever I needs to find out any particular sell made by which salesman; I am not able to get if the data is voluminous. So I will require your help to deal with such a situations.
i have a table with Requestor names linked to cost center numbers. Each requestor and cost center are assigned to a Financial Analyst. What formula can i use to look up seversl requestors per cost center tied to a Financial Analyst?
1. Who sold second highest?
Answer: INDEX(B5:B17,MATCH(LARGE(D5:D17,2),D5:D17,0))
2. What is the Profit Ratio rank of person who sold second highest?
Answer: RANK(INDEX(F5:F17,MATCH(LARGE(D5:D17,2),D5:D17,0)),F5:F17)
3. 3. What is difference in sale per customer between the highest selling & lowest selling sales persons?
Answer: LARGE(D5:D17,1)/INDEX(C5:C17,MATCH(LARGE(D5:D17,1),D5:D17,0))-LARGE(D5:D17,2)/INDEX(C5:C17,MATCH(LARGE(D5:D17,2),D5:D17,0))
I believe that the task was to take into consideration lowest and highest sales and for the second part using LARGE(D5:D17,2) means you took into consideration the second highest value..
Hope i got it right because i havent used the large fct before
need to lookup a value in one sheet find in 2nd sheet then find text or value same as vlookup but too the left…
select value in column A in first sheet
lookup corresponding value in 2nd sheet in column U
find specific text or value to left
same as vlookup but to the left using 2 sheets…
got it using index… thx…
thank you so much. this is very help me ^_^
Hi, the column F contains 2 values with 36%. What do I do if I want to see what persons have ration of 36%?
Would I use the seperate funcions includiong LARGE?
I found reply to this type of questions, I saw it reoccuring many times. So for those hungry for an answer – what happens if there’s multiple values for the same reference?
Please look at that link, the unswer is under poing 4
http://chandoo.org/wp/2008/06/09/what-the-if-learn-6-cool-things-you-can-do-with-excel-if-functions/
Hello,
Thanks for the above but e.g. how does one provide a formula using index and match for a budget, and then bring back all the data in e.g. April for 2013 then compare same thing with April 13 budget so a snapshot can be provide analysing just the month’s Actuals and the Month’s budget?
Then a simple variance can be found on one sheet.
Many Thanks
@Mohammed
Are you able to post a sample file with some budget and actual data so that i can give you a specific answer
Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook