How to Lookup Values to Left?

Posted on November 2nd, 2010 in Excel Howtos , Learn Excel - 82 comments

This article is part of our VLOOKUP Week. Read more.

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:

Data for this Example -Make VLOOKUP go 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,…

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:

Data for this Example -Make VLOOKUP go Left

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

VLOOKUP Week @ Chandoo.org - Learn tips on lookup formulas in Excel

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

82 Responses to “How to Lookup Values to Left?”

  1. Jason says:

    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))

    • satendra kumar says:

      Dear I want left site value by vlookup,

      please share me,

      Thankx
      satendra kumar

    • mybadongt says:

      If there are same values in the Index-Match-Large values and you use the Largest “n” values, it tends to stop in the first same values it finds. How can one make it select the any of the next similar largest “n” value..???

  2. Arman says:

    Sir, for example #1 you made, what if there are more than 1 person who has sales of 1088? What will be the result?

  3. Ram Kapoor says:

    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))

  4. jeff weir says:

    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)

    • James Perry says:

      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

  5. Mike says:

    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

    • saransh says:

      hi Mike, thnx for formula.
      but can you please explain this logic of this formula, similar to explained by chandoo once in simple english.

      thnx in anticipation

      regrds,
      saransh

    • Sanjay says:

      Could you pl explai the choose function
      CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17

  6. msk says:

    Hi Mike,
    I have tried your formula
    Its working and I am really happy…
    thks a lot..

  7. sunil says:

    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.

  8. hellomoto says:

    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)

  9. Mike says:

    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

  10. hellomoto says:

    Mike,

    What did you mean by enter with ctrl+alt+delete?

    I tried that formula but got the #value error. BTW, using excel 2003.

  11. Mike says:

    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

  12. Chandoo says:

    It should be CTRL+SHIFT+ENTER

  13. hellomoto says:

    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

  14. Chandoo says:

    & is used to concatenate 2 values. So if F3 has “a” and G3 has “b”, F3&G3 will be “ab”.

  15. hellomoto says:

    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!

  16. jilllou says:

    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.

  17. Amit says:

    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.

  18. Sajid says:

    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?

  19. [...] This is a bit tricky, but easy once you figure out the formula. We will use INDEX+MATCH. [...]

  20. Pratik Mehta says:

    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)))

  21. [...] 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() [...]

  22. GSS says:

    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.

  23. Hui... says:

    @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

  24. Adell says:

    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?

  25. Adell says:

    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.

  26. steve says:

    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

  27. [...] used: INDEX | OFFSET| INDIRECT | Array Formulas |  Using Date & Time in Excel Spread some love,It [...]

  28. Ray says:

    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…

  29. Sophia says:

    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))

    • Ania says:

      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

    • Roger says:

      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)…

  30. Beth Morrison says:

    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

  31. [...] 2010 Chandoo wrote a post about options to perform a VLookup to the left of the Reference [...]

  32. Yogesh Darne (YD) says:

    Dear Chandoo
    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 :) sample file for your reference http://speedy.sh/2tyra/lookup-across-spreadsheet.xlsx
     
    thanks a ton for this awesome world dedicated to excel :)
     

  33. Anurag Singh says:

    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 

  34. Anurag Singh says:

    Please disregard my earlier message just saw the answers in comments.

  35. Anurag Singh says:

    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.

    • Kampungguy says:

      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
       
       

    • Kampungguy says:

      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.

  36. Kiwi Brown says:

    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!

  37. Rinto Antony says:

    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

  38. Rajesh says:

    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
     

  39. CA ANIRUDDHA GHUDE says:

    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.  

  40. rodney moore says:

    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?

  41. Sandip Agarwal says:

    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))

    • juliana says:

      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

  42. Bill says:

    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…

  43. Bill says:

    got it using index…  thx…

  44. dwie okta says:

    thank you so much. this is very help me ^_^

  45. Ania says:

    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?

  46. Mohammed says:

    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

  47. Matt says:

    How do I do a lookup with multiple criteria. My data looks something like this..

    colmnA colmnb colmnc
    Item state quantity
    A CA 10
    A IL 64
    A AL 34
    A OR 42
    B IL 42
    B CA 87

    If I wanted to find out the quantity of item A in CA what formula would I use? Is this an index match match? An example formula would be great. Thanks.

  48. jon says:

    why not the lookup fuction its so much easier.
    lookup(look up value, lookup vector, results vector)

  49. rahul says:

    plz make easy type solution and make me help

  50. Luke says:

    Hi Chandoo / All,

    I am trying to use the index function, with the table array set as a defined name table. However, I am trying to have the table array syntax looking at a single cell which contains the named table and this is the data I want to use for my INDEX function.

    Is this possible or is this a lost cause?

    any help / advice welcome.
    thank you!

  51. Sree Harsha says:

    Chandoo sir, it is good to see the lookup value to the left, but is there any way to setup two conditions for looking the value.

    Like in case of sumifs, we can give more conditions na.

  52. Michelle says:

    Help, I cannot find a forum that has already answered my query.

    In Tab1 have names in column A. In Tab2 I have the sames names in column B but duplicated. In Tab 2 column C I have values.

    Basically, In tab 1, I am trying to match the names from Tab2 ColumnB and return the highest value from Tab2 Column C.

    Can anyone help me?

    • Chandoo says:

      @Michelle… Welcome to Chandoo.org and thanks for your comments.

      Assuming in tab1, the values are from A1,
      You can use MAX formula to do this.
      =MAX((tab2!$B$1:$B$100=tab1!A1)*(tab2!$c$1:$c$100))

      Press CTRL+Shift+Enter after typing this formula.

      If you have Excel 2010 or above, you can also use:

      =AGGREGATE(14,4,(tab2!$B$1:$B$100=tab1!A1)*(tab2!$c$1:$c$100),1)

      This can be entered without ctrl+shift.

  53. Imran Shaikh says:

    Dear Chandoo,

    How to use conditions in INDEX+MATCH function ?

    Eg : I have Group in Column A, Co in Column B, Circle in Column C, and Vendor in Column D, Sales in Column E. Now matching all criteria s of Columns A,B,C & D the result will be Column E (Sales).

    Please Help

    Thanks in advance

  54. Nicole says:

    Im using your below formula.. but when I click F2 and F9.. the formulas were gone.. how can retain the equation.

    =INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))
    & ” (“&MAX(COUNTIF(B2:K2,B2:K2))&” times)”

  55. Geoff says:

    I have a range of columns containing formulas and numbers, headed 1 , 2, 3, etc.

    I want to enter, say, “3″ and get the range of fields in column 3 copied to another sheet.

    I cannot see how to do this with VLOOKUP and am not sure how to do it.

    Can anyone give me the formula to do this.. I am a newbie?

Leave a Reply