# How to Lookup Values to Left?

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

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

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

 Mix VLOOKUP with Data Validation for some magic! [VLOOKUP Week] How to Look up Based on Multiple Conditions
 Written by Chandoo Tags: downloads, homework, INDEX(), Learn Excel, MATCH(), Microsoft Excel Formulas, spreadsheets, vlookup, vlookup week Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 152 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,

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

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!

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?

• Petar says:

I think that, in this case, formula will show the first one that is in the column and have 1088 value.

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

=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,
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

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

• Gabriel says:

=CONCATENATE("a","b","c") ? ="a"&"b"&"c"

• Gabriel says:

? ==> I Wrote eqvialent ( The special sing)

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.

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

• Deepak says:

thanks a lot....

• shazia siddiq says:

Hi Pratick,

What do the values 2 and 0 stand for in the closed parentheses?

• Swathi says:

The second largest

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

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.

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.

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

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:

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

• Lucky says:

hi Chandoo,

I think we can use  =lookup(A1,{0,51,100},{"Fail","Pass","Pass"})  also.

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?
2. What is the Profit Ratio rank of person who sold second highest?
3. 3. What is difference in sale per customer between the highest selling & lowest selling sales persons?

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

• Hui... says:

@Matt

=Sumproduct((A1:A10="A")*(B1:B10="CA")*(C1:C10))

48. [...] can use INDEX + MATCH formulas to do [...]

49. jon says:

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

50. […] For more tips read using INDEX + MATCH combination […]

51. rahul says:

plz make easy type solution and make me help

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

thank you!

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

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

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.

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

• Hui... says:

@Imran
Can you please post a sample file
Hui...

• Imran Shaikh says:

Dear Hui,

DATA
Group Company Circle Vendor Sales
X A NORTH TTC 2000
Y B SOUTH ABC 3000
X A EAST TTC 6000
Y B WEST XYY 7000

RESULT
C -1 C-2 C-3 C-4 RESULT
Group Company Circle Vendor Sales
X A EAST TTC 6000

Now I have Criteria 1,2,3 & 4. I will check this criteria from above data and populate the sales figure with one formula. Hope You got my point.

• Imran Shaikh says:
• Hui... says:

@Imran

I am unsure what the relationship between C1, c2 etc is to the data

However why not use an Advanced Filter?

• Imran Shaikh says:

@Hui
Thanks for your reply. I got solution for this. I have used "DGET" function instead of index+match. 🙂

56. 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)"

57. […] INDEX + MATCH formula combination […]

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

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

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

61. […] 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. […]

62. arvind singh says:

thanks
arvind

63. Amidala Sue says:

Hi Chando.

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

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

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

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

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

68. 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) –

• Hui... says:

@Matt
1. Lookup to the left
2. Correctly access unsorted data

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

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

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

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

Regards,
Prajakta

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

• Debbie says:

Never mind I figured it out. 🙂

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

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

• Artine says:

By this I mean that you can return a value from Column 1. So, yes, VLOOKUP can come to the rescue.

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

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

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

78. lingeshwaran says:

hi bro,

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

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

80. Hoyt Pasculli says:

Good information right here!

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

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

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,

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

85. 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)}

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

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.

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

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

• Man1ak says:

Np,

you are looking for solution with index and match?

• Khurrum says:

Yes Man1ak, Index and Match is required since it will work with other queries as well

• Khurrum says:

Thanx Man1ak

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.

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

90. 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))}

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

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

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

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

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

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

 Mix VLOOKUP with Data Validation for some magic! [VLOOKUP Week] How to Look up Based on Multiple Conditions