fbpx
Search
Close this search box.

How to Lookup Values to Left?

Share

Facebook
Twitter
LinkedIn
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

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

168 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

      • Amit Kumar says:

        NAME NO. NO. NAME
        Amit 100609 100602
        sumit 100602
        ankit 100604
        ravi 100605
        shyam 100607
        gaurav 100606
        vijay 100603

        =VLOOKUP(H2,CHOOSE({1,2},$C$2:$C$8,$B$2:$B$8),2,0)
        Put this formula below NAME Column, it will work....

    • 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..???

      • JKirch says:

        I would like to perfom something similiar. What I am trying to do is extract number of pieces per person, the row goes from left to right and as data is entered I want it to populate and keep track of a rolling total for each month. Can you help?

    • Melissa says:

      Hello Jason,

      One question. What if in this case ( if you want to find-out who made $1088) there is another person besides John? Does this formula only work for unique characters?

      Thank you!

    • Priya Dutt says:

      Hi Jason,

      For Homework question 2: I have also used the same formula but I am getting value "Zero" as an answer. I think I am doing something wrong, Please tell me how can I correct.
      =INDEX($F$5:$F$17,MATCH(LARGE($D$5:$D$17,2),$D$5:$D$17,0),1,1)

      For Question 3: What if we use below formula and what is the difference:
      =MAX(D5:D17)/MAX(C5:C17)-MIN(D5:D17)/MIN(C5:C17)

      Please answer my query for clarity.

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

    • Sunil Lokhande says:

      Please send me formula as given below:

      • Sunil Lokhande 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.

  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

    • sushil says:

      ctrl+shift+enter ----> please tell me more about it i am lost in it and cant be choked forever without knowing it

      - Sushil

      • Gabriel says:

        sushil,

        ctrl+shift+enter refers to a combination of keys you press just like when you do crtl+alt+delete to reset your computer

        in this case it will "unlock" the array function of the formula also known as formula validation.

        If you clic the cell you will then see that the formula is now between {...}.

        This type of formula needs a validation process which is what the key sequence actually does. If you need to modify the formula, you can, but remember to "validate" it again once you are done.

        - Gabriel

  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

    • Kumar says:

      Hi,

      Could you explain me the logic of the formula used for - 3. What is difference in sale per customer between the highest selling & lowest selling sales persons?

      Thanks,
      Kumar

      • Hui... says:

        @Kumar
        I don't believe the answer given above by Sandip is correct
        I think it should be
        =(MAX(D5:D17)/INDEX(C5:C17,MATCH(MAX(D5:D17),D5:D17,0)))-(MIN(D5:D17)/INDEX(C5:C17,MATCH(MIN(D5:D17),D5:D17,0)))

        Which is easy to work through and understand

  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?

  56. Usman Mushtaq says:

    I have a database where there are 2 cities. Against one city i want rates to be applied on 0.5 Kg as 15 and if the consignment wt is 1 kg then rates applied are to be 20 which will carry on subsequent kgs.
    However in case of city 2, the rates for 0.5 Kg is 20 and if the consginment wt is 1 kg then rate applied is to be 30.

    Please suggest the formula with syntax to help me come out of this situation.

  57. Heather says:

    Hello. I have a doozy! I have a list of names in column A, a list of numbers in column B, and in column C I would like to produce a list of the names from column A that correspond to a specific number in column B, but I would like that list to not have any rows that are blank. Try this:
    Column A (place one of these names in each row of column A): Ashley, Heather, Brian, Mike, Brad, Jim, Julie, Alex, Lynne, Janine
    Column B (place one number in each row of column B): 1, 2, 1, 1, 2, 0, 2, 1, 0, 1
    Column C (these are the results I'm looking for with a formula of some kind): Ashley, Brian, Mike, Alex, Janine

    Please let me know if there's something out there that will accomplish this! Thank you so much! I've looked everywhere. I can send you a spreadsheet if you need!

    • Chandoo says:

      @Heather... I am not sure understand the logic behind how names are fetched in column C. How to interpret the numbers 1,2,1,1,2... to get the names Ashley, Brian, Mike, Alex, Janine..?

    • MURALI says:

      use this formula in cell C2: =IF(B2="","",A2), to get the name from A2, if B2 is NOT blank. Copy the formula to other cells.

  58. […] VLOOKUP is one of the most popular excel formulas that exists. Looking up and finding data is one of the most common uses of excel and VLOOKUP is many an excel user’s go-to formula. VLOOKUP has one very annoying limitation however; it can only search to the right of the table. […]

  59. arvind singh says:

    please send me your query and answers

    thanks
    arvind

  60. Amidala Sue says:

    Hi Chando.

    Appreciate your website. im not an expert though.
    Can you further help me with my data please

  61. Vikram says:

    Hi All,
    Using Index+Match function can i return 2 or more values. As per the above example can i list sales person who have done >1000.

  62. Diana Imelda says:

    Hi.. I have a excel question..

    If I have a data
    A 700
    B 0
    C 800
    D 0

    and I would like to summarise the data to consist of the one that has value become this:
    A 700
    C 800
    (with no B and D), how to make it that way?

  63. Sonja says:

    Hi..I use the =INDEX($B$5:$B$17,MATCH(1088,$D$5:$D$17,0)), but $D$5:$D$17 has a data validation rule of defining a list of values already. Due to that i get an error "a user has restricted values that can be entered into this cell".

    How do i get rid of that error, without deleting the data validation ?

    Please assist.

  64. sigit says:

    you can use if.
    =IF(D2=1088;B2;"") ===> RESULT = JOHN.
    then you copy the formula down.

    this can be used to find anyone with the sales number of 2,088.
    just that simple

  65. Matt says:

    What can Index Match do that vlookup can’t?

    First let me start with that vlookup is a bit simpler and quicker to put together and has its time and place.

    Sometimes though, we need to lookup a value that might be to the left of the search column (remember, vlookup only searches from left to right).

    Rumour has it that Index Match also works faster in big spreadsheets but I don’t notice much of a difference.

    Here’s the structure of the Index Match formula:

    =index(WhichColumnToReturnValueFrom , (match(SearchForValue, SearchColumn, 0)

    =INDEX ( Column I want a return value from , ( MATCH ( My Lookup Value , Column I want to Lookup against , ZeroForExactMatch)

    So an example would be if we had a student name and wanted to look up their student number (column to the left) –

    Source: http://www.excelvlookuphelp.com

  66. vamsi says:

    whenever i am unclear what to do,whenever i am clue less,whenever i search for easier way to do things in excel i run google search that routes me to chandoo then he meets my expectations like ever.

    it makes me feel like "we are gifted with such an intelligent"

  67. Matthew Jackson-Cook says:

    Very stuck at the moment with index match, can you give me a hand?
    I have a stocklist for my business, but we buy all our stock off a business who send us a stock sheet every day with a larger amount of products (including all the ones to be included in my stocklist but with additional products which do not need to be added) so let's say my stocksheet is sheet 1, and the larger stocksheet which I will copy stockcount from is sheet 2; I require a formula that will match the product code from sheet 1 to sheet 2, and return the stock count from sheet 2 back to sheet 1 in the column 'stock level' which will appear to the right of the product code - I followed your tutorial and it returned 'N/A' after entering =INDEX(Sheet2!B1:B4177,MATCH(Sheet2!A1635,Sheet2!A1:A4177, 0))
    Any help?? Thanks!!

    • Chandoo says:

      Hi Matthew... thanks for your comments. My guess would be the stock code is not present in Sheet 2.

      Here is what I suggest:

      1. Memorize a stock code that is returning NA
      2. Go to Sheet 2 and press CTRL+F
      3. Type the stock code and search for it
      4. If you find the matching entry, make sure the cell in Sheet 2 does not contain any extra spaces or blank characters at the end of stock code
      5. I am guessing your sheet 2 data may have some extra characters (like blank spaces or invisible chars) at the end or beginning of the codes. If so, you will have to remove them by either using =TRIM(CLEAN(Sheet2!B1)) or manually.

      Also, make sure your references are absolute.

  68. Prajakta says:

    hi,

    I need help...
    I have data, with list of customers. lets say- 10 customers.
    there are 10 projects per customer.

    I want to lookup the project per customer. as the customer name is repeated in the master data, I can not use vlookup.

    EX.
    CustomerA- Project1
    Project2
    Project3
    Project4... Project10
    ...
    ...
    Project18
    CutomerB- Proj1
    Proj2

    Now if I am selecting CustomerA it must show me all 10 Projects in the next column.

    Awaiting for reply.

    In case, If its required to share the data, please let me konw.

    Regards,
    Prajakta

  69. Debbie says:

    Hello,

    I am looking for help with writing a formula in an excel workbook.

    On the first worksheet I am inputting for example:

    Column A Column B Column C
    Employee Name Budget Amount Budget Code
    Amy 200.00 01.6500.0.5770
    Bob 150.00 01.6510.0.5710

    On worksheet 2 I have a lot more information, but would like the information from the first worksheet to auto populate the budget amount and budget code once I type the employee's name.

    The columns A, B & C on worksheet 2 are titled the same as worksheet one.

    Can anyone help?

  70. Micheal Poh says:

    Homework:

    1.=INDEX($B$5:$B$17,MATCH(LARGE($D$5:$D$17,2),$D$5:$D$17,0))
    2. =RANK(F15,INDEX(F5:F17,MATCH(LARGE(D5:D17,2),D5:D17,0)))
    (The ranking of second highest is 3, but my formula get 1, please assist
    to amend if anything goes wrong)

    3.=INDEX(D5:D17,MATCH(MAX(D5:D17),D5:D17,0))/INDEX(C5:C17,MATCH(MAX(D5:D17),D5:D17,0))-INDEX(D5:D17,MATCH(MIN(D5:D17),D5:D17,0))/INDEX(C5:C17,MATCH(MIN(D5:D17),D5:D17,0))

    At the 3rd question, my logic is use the INDEX + MATCH to find the highest selling sale per people and lowest sale per people. Use the highest - lowest and get the difference is 179.

    • Micheal Poh says:

      Homework:

      Finally, i found the correct way to get the answer.

      2. =RANK(INDEX(F5:F17,MATCH(LARGE(D5:D17,2),D5:D17,0)),F5:F17)

  71. Artine says:

    Very informative article!

    Forgive my ignorance here (only recently started diving into Excel), but when do you ever actually really need a formula to lookup values to the left? You're always specifying a range, so VLOOKUP would have worked fine in the example table, if you specify that whole table as the range. Since you're always specifying ranges, when do you ever really need to go left?

  72. […] Excel purists will scoff at this solution. Yes, there are more elegant ways around this problem. As often, here I defer to Chandoo. […]

  73. DASTAGIRI says:

    in "MATCH" Function there is limitation. if u have a table like duplicate values in column 1,2,5,7,9,2,6,7,...... if u apply match function for this first it will show row number 1 and u will drag for further rows
    out put like; 1row,2row,3row,4row,5row,2row,7row,4row
    the output value depends on what the input we have...
    can you tell me about this solutions "chandoo" anna.

  74. Hemant Bhargava says:

    Dears,

    I want a formula for table below
    Asset Value Rate of Depeciation Amount of Depreciation
    Car 500000 15% 75000
    Building 1000000 10% 100000
    Machine 700000 15% 105000
    Furniture 100000 10% 10000
    if i have two columns in another table
    e. g. Asset and value
    and I write car in cell A2 the cell B2 should automatically fill with its value 500000

  75. lingeshwaran says:

    hi bro,

    Could you please help me to solve this.

    i have 2 excel books. In first book i have 2 columns.In the first column i have names and second column i have countries working. I want to create a formula in second book such that if i type the name of the person i should get the countries working

  76. Nour says:

    hello,
    i have a problem if for example
    Item Price Starting Ending
    dtf8693 100 1/10/2013 1/1/2014
    dtf8693 110 2/1/2014 3/2/2014
    the above represents the price list the below are sales i want to find what price that corresponds to item on a date that falls between to find the discount amount is there a formula
    dtf8693 1 50 1/11/2013
    dtf8693 1 55 5/1/2014

  77. Hoyt Pasculli says:

    Good information right here!

  78. matt says:

    How do I index match to create a unique list from a column that pulls from 2 other columns using left/mid/right?

  79. Aung Htike says:

    Hi chandoo,
    I have a problem!!
    how to use FIFO method in excel. Is there any formula for FIFO(first in first out).
    plz help me with sample file!!

  80. GHAZI IRSHAD says:

    i want to use suitable formula for
    match sheet1 cell value to sheet2 cell range and if value is correct then print sheet2 next column text with commas,

  81. Melissa says:

    Hello,

    One question. What if in this case ( if you want to find-out who made $1088) there is another person besides John? Does this formula only work for unique characters?

    Thank you!

  82. Man1ak says:

    Homework Question 1:

    1. =VLOOKUP(LARGE(D5:D17,2),CHOOSE({1,2},D5:D17,B5:B17),2,0)
    1. {=INDEX(B5:B17,MATCH(1,(D5:D17=LARGE(D5:D17,2))*1,0))}
    1. {=+VLOOKUP(TRUE,CHOOSE({1,2},(D5:D17=LARGE(D5:D17,2)),B5:B17),2,0)}

  83. MasterExcel says:

    I really love Index Match! that is much better than vlookup formula! It far more practical, less bugs and integration with Name Manager as well!

    Index Match for ever!

  84. Ranadeep says:

    Chandoo..

    For Question : Who sold to minimum number of customers?

    What is the difference between the following two formula in tour excel attached.
    Formula 1 : =INDEX($B$5:$B$17,MIN($C$5:$C$17))
    ANS: Johnson

    Formula 2: =INDEX($B$5:$B$17,MATCH(MIN($C$5:$C$17),$C$5:$C$17,0))
    ANS : Jessy

    Can you please let me know the exact use of MATCH function. why it is giving different ans.

  85. Khurrum says:

    Seller = Qty Sold

    A = 5
    B = 6
    C = 8
    A = 4
    B = 7
    C = 6
    D = 20
    E = 15
    A = 5
    C = 9
    B = 2
    E = 5

    Q How to sum up the highest/lowest sales quantity achieved by respective salesman.

    Highest Total Sales Qty & Name of Seller = ?
    Lowest Total Sales Qty & Name of Seller = ?

    Using index and match with max and min and sumif or sumproduct?

    Please no array formula

    • Hui... says:

      @Khurrum

      The latest version of Excel 365 have a MaxIf and MinIf functions
      I'm not sure if they have made it to Excel 2016 yet

    • Man1ak says:

      Hi, I've used array formula without helper column

      14 & A {=CONCATENATE((MIN(SUMIF(B2:B13,B2:B13,C2:C13)))," & ",INDEX(B:B,IF(ISNUMBER(MIN(SUMIF(B2:B13,B2:B13,C2:C13))),ROW(C2:C13),"")))}
      23 & A {=CONCATENATE((MAX(SUMIF(B2:B13,B2:B13,C2:C13)))," & ",INDEX(B:B,IF(ISNUMBER(MAX(SUMIF(B2:B13,B2:B13,C2:C13))),ROW(C2:C13),"")))}

      • Man1ak says:

        EDITED:
        14 & A
        {=CONCATENATE(MIN(SUMIF(B2:B13,B2:B13,C2:C13))," & ",VLOOKUP(MIN(SUMIF(B2:B13,B2:B13,C2:C13)),CHOOSE({2,1},B2:B13,SUMIF(B2:B13,B2:B13,C2:C13)),2,0))}

        23 & C
        {=CONCATENATE(MAX(SUMIF(B2:B13,B2:B13,C2:C13))," & ",VLOOKUP(MAX(SUMIF(B2:B13,B2:B13,C2:C13)),CHOOSE({2,1},B2:B13,SUMIF(B2:B13,B2:B13,C2:C13)),2,0))}

        • Khurrum says:

          Thanx Man1ak, it works well.

          Hope it will work with index and match w/o array if there is some one who resolve it. It is not an argument but consideration for the user.

      • Khurrum says:

        Thanx Man1ak

        Your are quiet close but answer is
        14 & A
        23 & C

        It is not a challenge but an honest query, do whatever it takes, without a helper column and kindly explain an array formula as well, once it works.

  86. Khurrum says:

    Hi Hui

    Yes, it doesn't available in Excel 2016 pro plus, 64 bit. as yet.

    I tried with the helper column it works perfect, but it is not I am looking for since I don't have enough room for helper column.

    I want a single cell formula which gives name & amount both simultaneously.

  87. Man1ak says:

    LEFT VLOOKUP SOLUTIONS:

    23 & C

    {=CONCATENATE(LARGE(SUMIF(D7:D18,D7:D18,E7:E18),1)," & ",VLOOKUP(TRUE,CHOOSE({1,2},(SUMIF(D7:D18,D7:D18,E7:E18)=LARGE(SUMIF(D7:D18,D7:D18,E7:E18),1)),D7:D18),2,0))}

    14 & A

    {=CONCATENATE(SMALL(SUMIF(D7:D18,D7:D18,E7:E18),1)," & ",VLOOKUP(TRUE,CHOOSE({1,2},(SUMIF(D7:D18,D7:D18,E7:E18)=SMALL(SUMIF(D7:D18,D7:D18,E7:E18),1)),D7:D18),2,0))}

  88. asif says:

    can I combine these two separate index match Fx into one.

    =INDEX($D:$D,MATCH(I51,$E:$E,0))
    =INDEX($A:$A,MATCH(I51,$B:$B,0))

  89. Raj says:

    220100013145
    5901551367
    1342010037890
    18401518190
    2531530002616
    4520100014705
    48010100197915
    70900101001792
    87501000001973
    10075966653
    11204964054
    12481600000011
    1488101001433
    1488101051471
    3011151082
    30704276397
    543102010004325
    6226000100023040
    624001532102
    624201525768
    624201526434
    874010100012330
    how to get spaces in suffix of numbers maximum to 17 digit

  90. Chandra says:

    Hi There,

    I have the data with like below.

    Dimensions are in different columns, however KPI/values columns are consistent. Could you please advise how to do lookup ?

    I'm using concatenate (4 dimensions) and then lookup but it is increasing file size as i have the data till 50k rows.

    Is there any other way to do it ?
    Please advise.

    country city device month sales units
    UK London All Feb 200 20
    US California All Feb 600 60

    city month device country sales units
    London Feb All UK 200 20
    California Feb All US 600 60

    Thanks,
    Chandu

  91. Will says:

    All,

    I see that Question 3 resulted in (1) Answer, however that is not fully correct. It was, "Who sold to the minimum number of customers?" There are (2) Answers for this question. Jessy and Jimmy both sold to (6) customers. However you formula produced only one answer of Jessy. How do you get a formula that produces both Jessy and Jimmy?

    Thanks,

  92. jatin gupta says:

    how can we enter a more then 15 digit a number without making the cell in text format , and how can we change this digit limit..

  93. Shri says:

    I need help in excel, i have huge data i want formulae such that sum of 3 variables match a total but the 3 variables should be in the range of 300 to 800.

    for example:
    target: 2200
    A=?
    B=?
    C=?
    i want formulaes in the place of ? such that the sum(A+B+C) matches the target. Also the condition is A,B and C should be any number between 300 to 800.

    Please help.

  94. Ken says:

    I need Excel to select a value depending on a random coin flip. The values are: 5, 10, 15, 20, 25, ... through 95. The beginning number is always 10. If the coin returns tails the number returned will always increment one level (plus 5, but never greater than 95). If the coin returns heads the number returned will always decrement one level (minus 5, but never less than 5). However, if the coin returns heads two out of any three consecutive tosses the number will be set to 10 and everything starts over.

    I'd like to perform this with INDEX/MATCH or VLOOKUP if possible. I've been able to get most of it, but the "two out of any three consecutive tosses" has got me stumped.

    Thanx,

    Ken

  95. VAndita says:

    Homework answers

    =INDEX($B$5:$B$17,MATCH(LARGE($D$5:$D$17,2),$D$5:$D$17,0))
    =RANK(INDEX($F$5:$F$17,MATCH(LARGE($D$5:$D$17,2),$D$5:$D$17,0)), F5:F17)
    =INDEX($D$5:$D$17,MATCH(MAX($D$5:$D$17),$D$5:$D$17,0)) /INDEX($C$5:$C$17,MATCH(MAX($D$5:$D$17),$D$5:$D$17,0))-INDEX($D$5:$D$17,MATCH(MIN($D$5:$D$17),$D$5:$D$17,0)) /INDEX($C$5:$C$17,MATCH(MIN($C$5:$C$17),$C$5:$C$17,0))

  96. Jansaya says:

    How to use Index function if i want to return entire rows that contain the name i am looking for?

  97. Ajay Jaiswal says:

    1.=INDEX(B5:B17,MATCH(LARGE(D5:D17,2),D5:D17,0),1)
    2.=RANK(INDEX(F5:F17,MATCH(LARGE(D5:D17,2),D5:D17,0),1),F5:F17,0)
    3.=INDEX(D5:D17,MATCH(MAX(D5:D17),D5:D17,0),1)/INDEX(C5:C17,MATCH(MAX(D5:D17),D5:D17,0),1)-INDEX(D5:D17,MATCH(MIN(D5:D17),D5:D17,0),1)/INDEX(C5:C17,MATCH(MIN(D5:D17),D5:D17,0),1)

  98. Sushmita Bhar says:

    1.=LOOKUP(LARGE(D5:D17,2),$D$5:$D$17,B4:B17)
    2.=RANK(VLOOKUP(J12,B5:F17,5,FALSE),F5:F17,0)
    3.=(MAX(D5:D17)/LOOKUP(MAX(D5:D17),D5:D17,C5:C17))-(SMALL(D5:D17,1)/INDEX(C5:C17,MATCH(MIN(D5:D17),D5:D17,0)))

  99. Gopi says:

    Hi Chandoo, I am having trouble in using INDEX+MATCH. I am practicing with a sample file to find the entire sales figure for 1 category of product. But whatever combination I try, I just get #N/A. Please help me out.

  100. Min says:

    Hi Chandoo, could you please help me.
    I have problems with Index and Match when it comes to different tables, example:
    Data tables belonging to different countries.

    I have an example file. How do I send to you so you could see where I am stuck? thanks!

  101. pranav Kumar says:

    what will be the formula for naming those people whose sales are greater than 0 or a particular number

  102. Ganesh Gite says:

    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?
    Not able to get out of this one.
    Please help how can be index match will be used to get this one

  103. suebelle62 says:

    I have 2 sheets with addresses. 1 is from fleet software that has address where parked, not exact address of job. Have a job address list that has job # and address. On the trip sheet I need to have a column fill in job # looking up the address from the job sheet and finding the street or closest.

Leave a Reply