This tutorial demonstrates the key strengths of Excel's INDEX / MATCH function that make it superior to VLOOKUP. You will find a number of formula examples that will help you easily cope with many complex tasks when VLOOKUP fails. Continue reading
by Svetlana Cheusheva, updated on
This tutorial demonstrates the key strengths of Excel's INDEX / MATCH function that make it superior to VLOOKUP. You will find a number of formula examples that will help you easily cope with many complex tasks when VLOOKUP fails. Continue reading
Comments page 19. Total comments: 614
Data range is given below. I am looking for formula while helps me
in looking up name against same value.
Data Range:
B2:B4 C2:C4
John 1
George 2
Stephen 2
Where Required:
B7:B9 C7:C9
1 ?
2 ?
2 ?
Hi!
I wanna know how to detect a label and copy all the data column from the label detected I have something like that...
sorry but I don`t have any idea to do it .-.
A B C D
1 vvcf vvcvc vcfvdf Icfgg
2 0 -0.25 0.48 -0.11
3 0 -0.25 0.48 -0.11
4 0 -0.25 0.48 -0.11
5 0 -0.25 0.48 -0.11
6 0 -0.25 0.48 -0.1
7 0 -0.25 0.48 -0.11
Great! Index Match with your explanatory examples - solved my excel issues. Thank you!
REGARDING: Look up with multiple criteria using INDEX MATCH
Your formula on 2 criteria works but to check that i understand WHY it works recreated your example in my Excel 2010 and I entered in a separate cell:
={(A2='Lookup table'!$A$2:$A$13)}
expecting it to evaluate to 1 or TRUE. However it evaluates to FALSE so although your overall formula works it shouldn't? both of the tests need to evaluate to 1 for it to find a match (zero * anything is zero or FALSE?)
Can anybody shed light on this?
I am also experiencing the same thing and using Excel 2010. Could this be an Excel 2010 issue?
Thanks a lot!
We have a pricelist that looks like this, each section is a drop down - so thickness and height has multiple selection and select no required has 8 off, 12 off, 24 off, 50 off, 150 off. I want to somehow when the customer selects each thing they want in the two cells it returns the price.
SELECT THICKNESS & HEIGHT
75 x 75 x 3 x 4M
SELECT N0. REQUIRED
8off
PRICE
No Product Price
8off 75 x 75 x 3 x 4M 147.63
12 off 75 x 75 x 3 x 4M 146.33
24 off 75 x 75 x 3 x 4M 145.03
50 off 75 x 75 x 3 x 4M 144.35
150 off 75 x 75 x 3 x 4M 143.94
Can anyone help? Thanks
Hi
How to get the year from the example "How to use INDEX MATCH to search by row and column values" ?
I would like to have the year 2015 on the header as a return value when looking for the number 321,225.
I keep getting a #N/A error.
I tried VLOOKUP and MATCH and it returned results but only after checking 64 cells.
=VLOOKUP(U4, 'Sheet1'!$A$1:$E$79, MATCH(T4,'Sheet1!A$1:E$1, 0), FALSE)
So then I went to INDEX MATCH and still returning #N/A
I have a Volume and MarketShare that need to be looked up and a dollar amount that needs to be returned.
The grid is on another sheet...
=INDEX('Sheet1'!A1:E79,MATCH(U4,'Sheet1'!$A$1:$E$79,MATCH(T4,'Sheet1'!$A$1:$E$1,0)))
What am I doing wrong?
There is an IF(AND) statement attached to the T column but like I said it was not a problem when I used VLOOKUP
Thank you in advance
Brilliant explanations, thank you.
Dear,
I am working in a transport company so i have to keep trips record up to date with the trucks numbers.so many times i have used your published formulas.
Now i need a formula to show the trucks in separate sheets "loaded & unloaded" only to put the loading and unloading dates.
if you will see, i can send you the file
Is there a way to use index-match like this vlookup fomula:
=IFERROR(VLOOKUP([Site ID],‘P:\Dropbox\Data Control Systems\[PROPERTY DETAIL SHEET MASTER.xlsm]Main_Frame_Table,COLUMN(Main_Frame_Table[CLIENT STATUS ACTIVE INACTIVE]),FALSE),"")
I would want to lookup a data from another folder.
Hi, I read the article - first off - terrific and incredibly helpful.
However, I do have a question that was not addressed.
I have a table (with headers) in J4:O15.
J5:J15 is vendor name, K4:O4 is item name. Data is K5:O15.
I have been trying multiple ways with MAX, INDEX, and MATCH to have a cell return both the Vendor and the Product based on the MAX function tried against K5:O15.
Is there a way to nest MAX with Match that I haven't understood?
TIA
Hi,
I have several tables with prices of different flights.
Different tables range from Airline1 - Adult rates, Airline 1 - Child rates, Airline 2 - Adult rates, Airline 2 - Child rates, and so on.
Now, the above doesn't seem to work as mine actually looks more like a distances table, with prices going horizontally and vertically.
So really I need to have an Index/Match formula but which knows how to choose from which table according to criteria you need.
For instance:
Airline 3 - Child rates- Mumbasa - Cairo - Result, each of these variables in a different column.
Can you help ?
Thanks in Advance.
I have a large spreadsheet that I audit monthly with customer transactions. I want to make sure a customer didn't have a second loan open before the previous loan was paid. The customer could have 5 loans on the spreadhsheet; I want to make sure that no customer loans overlap. Each customer will have their own ID number and each loan is one row of data. For example, John Doe gets a loan on 10/2/12 and it's paid off on 10/31/12, but gets another loan on 10/30/12. The second loan was done before the first loan was paid. There could be a 100,000 rows of transaction. Can you help with a formula?
Hi,
i have two cell which is created time & date in one cell & next cell i have closed time and date, in this scenario i would required your help to count only working hours which 8:00 AM to 8:00 PM excluding sundays we required number of hours has been taken to closed the case excluding non working hours & sunday, please help.
I'm having trouble get an "and" statement to work. I have the existing formula
=IF(ISNUMBER(SEARCH("-08",B562)),139,IF(ISNUMBER(SEARCH("-11-",B562)),139,IF(ISNUMBER(SEARCH("-15-",B562)),139,IF(ISNUMBER(SEARCH("-20-",B562)),139,IF(ISNUMBER(SEARCH("-30-",B562)),159)))))
But I need to see if that same source cell has a "P" included. If so then the above values would apply and if the value in the source cell is "S" then a different value would apply.
If I understand your task correctly, then please use the formula below:
=IF( OR(ISNUMBER(SEARCH("-08",B562)), ISNUMBER(SEARCH("-11-",B562)), ISNUMBER(SEARCH("-15-",B562)), ISNUMBER(SEARCH("-20-",B562)), ISNUMBER(SEARCH("-30-",B562)) ), IF( ISNUMBER(SEARCH("P",B562)), 139, IF(ISNUMBER(SEARCH("S",B562)), 999, 555)), 159)
The result will be as follows:
139 – the cell contains at least one value from this list: "-08", "-11-", "-15-", "-20-", "-30-" AND contains "P"
999 – the cell contains at least one value from this list: "-08", "-11-", "-15-", "-20-", "-30-" AND contains "S"
555 – the cell contains at least one value from this list: "-08", "-11-", "-15-", "-20-", "-30-", but does not contain "S" or "P"
159 – the cell contains nothing from this list: "-08", "-11-", "-15-", "-20-", "-30-" AND contains "P"
Hi Svetlana,
Hoping you can help me with this. I have 4 columns: Action, target date, start date, completion date. In a new table, I would like to have 5 cells in a column show the 5 text entries from the Action column that corresponding to the earliest target dates that have blank completion date cells, and have these listed in ascending date order. To the right of this 5-cell column, I would like to have the target dates corresponding to each of these actions appear. I'm not sure how to combine all of the logic elements, including having the dates recognized. I'd love some help! Thank you,
Miranda
Hello Miranda,
Please send a small sample workbook with your source data to support@ablebits.com and include the result you expect to get. Our support team will look into your task and try to help.
Hi
how can i have sorted data from large data bank in a different sheet. for example -
Date name collection $
1/01 A 100
1/01 B 50
2/01 c 200
2/01 D 20
02/01 A 40
03/01 A 100... and so on...
say this is my data sheet. now i want to make a different sheet knowing what is collection detail of A. how can i have result like following in a different sheet-
01/01 100
02/01 40
03/01 100... like this.
Please help me providing your valuable comment. Thanx in advance...
Hello Rony,
The best approach is using a pivot table. Just add the Name column to the Filters group, Date column to the Rows group, and
"collection $" column to the Values group.
You can find more information in our PivotTable tutorial.
hey, i don't know what formula in excel to be use. this is the data.
A1-CODE B1-ITEM
A2-SWOO1 B2-SWEATER
A3-JK001 B3-JACKET
A4-PN001 B4-PANTS
A5-SW002 B5-SWEATER
A6-JK002 B6-JACKET
A7-PN002 B7-PANTS
A8-SW003 B8-SWEATER
A9-JK003 B9-JACKET
A10-PN003 B10-PANTS
Wondering if you can help. I have a data set that lists dates / Sales person names and email addresses they acquired - among a slew of other data. I want to use a formula to create a list of the emails captured by an individual salesperson on a given day (of which there will be many). Example: reference a date: 1/21/15 and a salesperson: John Smith and get a list of the emails for that date. Rinse and repeat for additional sales staff. ideas?
you have your MIN and MAX examples backwards...right?
Hi,
I would like your assistance in understanding the use of INDEX MATCH.
I have two sets of data.One has NAME, ACCOUNT NUMBER, ID NUMBER and EMPLOYEE NAME.
The other has NAME, ID NUMBER and EMPLOYEE name.
I need to match the names and then I get the corresponding account numbers.
Hi, Firstly, I thank you for providing this excellent service. You are doing a great service.I need some help with this. I want to send/link cells or rows to another worksheet/book when they meet certain criteria.
For example, if the date and time attended in column D and E is later than a day or 10 hrs compared to column B and C and link those data that match criteria to another sheet.
Thank you and hope to hear from you soon.
Hi,
Please can you advise how to solve with Index & Match for 2 rows & 1 column criteria as follows
Row ---- Employee Contribution
Row ---- Employer Contribution
Column --- Employee No.
Please advise how could I bring the values from data in another sheet using Index & Match or any other alternative function.
thanks and regards,
Santosh
Svetlana,
I think I have the formula correct for the Index/Match with multiple criteria, however I can't seem to make the { } register and execute the formula.
Running Excel 2010
{=INDEX([19122014.xlsx]Sheet2!$A$1:$N$24596, MATCH(1,(C3=[19122014.xlsx]Sheet2!$B$2:$B$24596) * (E3=[19122014.xlsx]Sheet2!$F$2:$F$24596),0),12)}
Please help.
liz
Hi Liz
I am new to this and was having the same issue. I believe you don't include the {} when typing in the formula and excel actually adds this in for you.
See 1:25 on this video http://www.businessinsider.com/excel-array-formulas-microsoft-2015-1
Hope this helps!
Hi Svetlana,
Need your expert help, I am new user to complex Excel functions as have never used anything beyond sum/average/max. I am trying to use Index/Match in a scenario where based on matching account in a cell, need to sum data from 12 months column in that row. similar to example you have given for Vlookup & sum function. But I am not able to figure out how to use it.
Is this correct Sumif?
=SUMIF(Trend!$A$6:$A$16,$J18,Trend!$C6:$N6)
It is giving only single cell value of Trend!$c6 instead of sum c6:n6
or I have to use Index/Match, if so then what will syntax?
Thanks in advance
regards
Piyush
I know it is silly.
I have two sheets with data that I need to match up in the following way:
Sheet 1:
Col A = Company name
Col B = Company address
Col C = Rent
Sheet 2:
Col A = Company name
Col B = Company address
The hard part is that on sheet 1 the list has different company names for the same address in multiple rows. Example:
ABC Inc, 123 Main St., $500
XYZ Inc, 456 Main Ave, $600
GHI Corp, 123 Main St, $550
I need to match the most recent (highest row number) or BOTH the company name and address with those on Sheet 2 and return the RENT from Sheet 1.
I have a feeling that this is going to require diving into VBA.
I have tried using INDEX and MATCH functions with helper columns but I can't get it to match BOTH cells.
Thanks
Hi Svetlana,
Great post.
Using your dateset for the 'How to use INDEX MATCH to search by row and column values example', how could you answer the question:
"By what year was the population of USA >= 300,000,000?" The answer should be 2015.
Essentially I have a very similar data set and I want to output a column title value, with the row title and a specific value as input.
Thanks!
Hugh
Hi there,
how could I do multiply array match? Thanks very much!
25 is the value to match with the data set below:
a b c d e f g h
60 60 60 60 60 60 60 60
120 120 120 120 120 120 120 120
200 200 200 200 200 200 200 200
80 110 110 110 110 110 110 110
25 25 25 25 25 25 25 25
25 25 25 25 25 25 25 25
22 22 22 22 22 22 22 22
70 70 70 70 70 70 70 70
120 120 120 120 120 120 120 120
50 80 80 80 80 80 80 80
10 15 15 15 15 15 15 15
15 15 15 15 15 15 15 15
Regards,
Jana
Hi Svetlana,
I am using the index/match function successfully, however I have a set of data that has the lookup value for the match function listed multiple times in the data set. Is there a way to choose the lookup value based on the value of the corresponding indexed data value. For example:
A B
1 Joe
2 Bob
3 Joe
4 Sally
5 Joe
How would I get the function to return the value 5 from column A rather than 1?
Thanks,
Chris
Hi Svetlana,
I am using the index/match function successfully, however I have a set of data that has the lookup value for the match function listed multiple times in the data set. Is there a way to choose the lookup value based on the value of the corresponding indexed data value. For example:
A B
1
Hi
I have a fleet of truck and want to manage daily expense on fuel my work is to dispatch goods from a depot to different centres
Hi there,
Thank you for the excelent tutorial.
I have been using Index Match function with success for many years.
Now I have come up with a new issue. I would like the column num in the function to pick only the second (or third or fourth) line of a cell instead of all of the cell data. Is that doable ?
Thanks
Nick
Hi All
I just recently discovered and learnt to love VLOOKUP and now I read about an even more powerful function. However, the core problem for me remains that in the end we are looking for exact matches here. If one has to be searching on text this is a fairly unrealistic precondition.
We have about 41k company names in our database of which some are in relation with others while others are not. Also, we still have dirty data which complicates the issue even more. So we'd have:
"ABC Company Inc." (Main Seat)
"ABC Company Germany"
"ABC Company USA"
"ABC Company" (dirty data record, actually main seat)
"Company ABC" (totally different company)
Now those companies can participate on fairs. From the fairs we receive a table of names of companies who participate - from their database. So the naming for the exact same company will very likely be different. The table from the fair exhibitor features up to 5 columns that I need on my master table from my database. On the master table I have a full company name and a short name which does not include business entity type. Also, our master table has a column with the website and so does the fair exhibitor table.
I would say that if 2 out of 3 prerequisites match (From Name, Short Name & Website; using wildcards on both ends of the search strings) I could perform the Index lookup. I can still not be completely sure that the result is 100% accurate but it should reduce the manual labor afterwards.
Do you have any insights or best practice advices for a scenario like mine?
Best
Roger
Hi Svetlana,
I was hoping that you might be able to help me with the functions to find the Max, Min and Average between two dates with a list of dates with data.
I cannot get the min function to work.
B C G H I J
2 1/01/2013 0:00 0.887 Max Min Ave
3 1/01/2013 0:00 0.952 1/01/2013 0:00 0.952 MIN(INDEX(($B$2:$B$50>=G3)*($B$2:B$50=G4)*($B$2:B$50=G3)*($B$2:B$50=G4)*($B$2:B$50<=G5)*$C$2:$C$50,))
5 1/01/2013 0:00 0.745 3/01/2013 0:00
6 1/01/2013 0:00 0.644 4/01/2013 0:00
7 1/01/2013 0:00 0.745
8 1/01/2013 0:00 0.845
9 1/01/2013 0:00 0.945
10 1/01/2013 0:00 0.845
11 1/01/2013 0:00 0.742
12 1/01/2013 0:00 0.842
13 1/01/2013 0:00 0.942
14 1/01/2013 0:00 0.842
15 1/01/2013 0:00 0.742
16 1/01/2013 0:00 0.642
17 1/01/2013 0:00 0.614
18 1/01/2013 0:00 0.542
19 1/01/2013 0:00 0.642
20 1/01/2013 0:00 0.542
21 1/01/2013 0:00 0.442
22 1/01/2013 0:00 0.341
23 1/01/2013 0:00 0.441
24 1/01/2013 0:00 0.541
25 1/01/2013 0:00 0.559
26 1/01/2013 0:00 0.550
27 1/01/2013 0:00 0.439
28 1/01/2013 0:00 0.339
29 1/01/2013 0:00 0.439
30 1/01/2013 0:00 0.539
31 1/01/2013 0:00 0.437
32 1/01/2013 0:00 0.336
33 1/01/2013 0:00 0.436
34 1/01/2013 0:00 0.536
35 2/01/2013 0:00 0.544
36 2/01/2013 0:00 0.433
37 2/01/2013 0:00 0.332
38 2/01/2013 0:00 0.432
Svetlana, thank you for your article. I hope you can help with a related problem I'm having!
In my worksheet titled “Data!” I have the following successful formula:
=SUM(INDIRECT(CHAR(64+MATCH(E3,A3:N3,0))&MATCH($B165,$A$1:$A$900,0)&":"&(CHAR(64+MATCH(E3,A3:N3,0))&MATCH($B165,$A$1:$A$900,0)+$O$2-1)))
Please don't bother with the details, as my problem is related to referencing another spreadsheet, as explained below. But in sum, the above formula works as intended by summing cells E124 to E133. In cell E3 I have the desired column name. In B165 I have the desired year.
I need the formula to be in a different worksheet. So, in my second worksheet I’ve written:
=SUM(INDIRECT("Data!"&(CHAR(64+MATCH(Data!E3,Data!A3:N3,0))&MATCH($O3,Data!A1:A900,0)&":"&"Data!"&(CHAR(64+MATCH(Data!E3,Data!A3:N3,0)&MATCH($O$3,Data!A1:A900,0)))))
However, this brings me a #VALUE error.
When I break the formula into two parts it works.
That is, this works:
CHAR(64+MATCH(Data!E3,Data!A3:N3,0))&MATCH($O3,Data!A1:A900,0)
And this works:
(CHAR(64+MATCH(Data!E3,Data!A3:N3,0)&MATCH($O$3,Data!A1:A900,0)))
But when I combine the two it breaks down. I suppose that the error has to do with the first instance of "Data!" below where I reference my initial worksheet.
=SUM(INDIRECT("Data!"&(CHAR(64+MATCH(Data!E3,Data!A3:N3,0))&MATCH($O3,Data!A1:A900,0)&":"&(CHAR(64+MATCH(Data!E3,Data!A3:N3,0)&MATCH($O$3,Data!A1:A900,0))))))
Any guidance would be greatly appreciated!!
Hello, I have been using the index/match formula for years to match data from multiple worksheets on a master sheet. However, after we updated to Excel 2013 the formula no longer works, I know I'm entering it in correctly but it does not return any data, no error code it just puts a 0 in the cell? Have you come across anyone else with this issue? And do you have any fixes for it?
thank you!
Hi Svetlana!!
I need help with an excel file....I have 2 seperate files with similiar data....Phone #'s and Department #'s on one and Phone #'s on another.....I want to move the Depatment #'s to the 2nd file using the Phone # as matches.....what kind of formula can do this.....Thx Joe
FYI - MATCH does not work with lookup strings greater than 255 either (as of 2013).
It also returns a VALUE error.
=INDEX(Sheet2!$A$2:$D$5049,MATCH(1,(BC2=Sheet2!$A$2:A5049) * (BD2=Sheet2!$B$2:B5049),0),4)
Sheet1 ( Column BE and BF needs to filled from Sheet2 matching the values in
Column BC and BD to A and B in sheet2)
BC BD BE BF
Bradbury 2560
BUSBY 2168
Sheet2
A B C D
BRADBURY 2560 -34.084468 150.829041
BUSBY 2168 -33.917587 150.899095
Hi Jag,
Your formula is correct, just use index 3 for column BE:
BE: =INDEX(Sheet2!$A$2:$D$5049,MATCH(1,(BC2=Sheet2!$A$2:A5049) * (BD2=Sheet2!$B$2:B5049),0),3)
BF: =INDEX(Sheet2!$A$2:$D$5049,MATCH(1,(BC2=Sheet2!$A$2:A5049) * (BD2=Sheet2!$B$2:B5049),0),4)
If you mean something different, please clarify.
Its not working for me. Below is the formula is used
=INDEX(Sheet2!$A$2:$D$5049,MATCH(1,(BC2=Sheet2!$A$2:$A$5049) * (BD2=Sheet2!$B$2:$B$5049),0),3)
Hi,
I have 2 rows
Type 1 6 6 1 2 3 7 8
Value 12 21 11 23 41 65 07 80
Enter 1
Result 12
Enter 6
Result 11
Enter 4
Result 12
Enter 9
Result 07
Basically I need to get the minimum value if the value matches or else minimum value of all the denominations that are lesser.
I would really appreciate f you can help me with this in any way.
Thanks got a way around :)
Hi
I've got a tricky one for you.
I've got a table with 5 columns. We've got various engineering disciplines - civil, mechanical, electrical etc. with various grades of engineers - senior, principal, technician etc. Each has a specific charge out rate for 3 different work locations. I want to link another work sheet and populate a row across the top using pull down lists so any of the 3 combinations on discipline, grade and office pops the right rate into the boxes so I can build up an estimate.
Any ideas?
Thank you for those explanations, you literally saved my life from hours and hours and more.. hours of work :o)
God bless you for giving us this explanation!
Hi there!
I have a worksheet with range (which is similar to a tax schedule) - Column A representing Minimum Value and Column B as Maximum values. Column C contains values if an amount is within the range of Column A and B. E.g.
Worksheet 1
Column A Column B Column C
1 100 50
101 200 75
201 300 100
Worksheet 2
78
220
115
What I would like is a formula/function to get the corresponding value from Column C in worksheet 1 if the value in worksheet 2 is within the Minimum and Maximum range from worksheet 1.
Thank you!
=INDEX(D2:D4,SUMPRODUCT(--(B7=B2:B4),ROW(A1:A3)))
A B C D
1 min max Output
2 1 100 50
3 101 200 75
4 201 300 100
how to find same value or number in excel.
how to find same value or number in excel
Hi,
For the example you showed on "Look up with multiple criteria using INDEX MATCH", what if my table's results are showed in horizontal instead of vertical? Is there any way I can make it look up and return the correct data without having to transpose the table? For example in the table below, I am trying to match the Lab and the Test and wanted it to return results for Test 1 for my first column and Test 2 for my second column. How do I make it to return the data in row 4 instead of column 3 like the example shown? Is there any way to achieve this? Thanks in advance!
Lab E Lab E Lab E Lab C Lab C Lab A
Ash Calcium Free L-CarnitineChlorideCobalt Copper
% % ppm % ppm ppm
Test #1 1.63 0.21 147.7 0.21 <0.20 4
Test #2 1.46 0.23 152.2 0.22 <0.20 4
Hi Angie,
When you are trying to match the Lab and the Test, do you have several Lab records for one Test? If you can send a sample workbook with your data to support@ablebits.com and include the result you expect to get, our support team will try to help.
Hi,
I want to compare two sheets sheet 1 and sheet 2,after comparing them both the sheets values inthe sheet should be merged and arranged in the ascending order.
can you please suggest me .
Thanks,
Please good you help with my formula.
I keep getting you've entered too many arguments for this function
=INDEX('Pivot by breachQH'!$A:$Z,MATCH($A$1,'Pivot by breachQH'!A:A,0),MATCH(B$4,'Pivot by breachQH'!$4:$4,0,MATCH("Majors-Lite",'Pivot by breachQH'!))))
Hi! Thanks for the very informative article!
I was wondering how you would alter the "Multiple Criteria Using INDEX MATCH" formula if, for instance, Dan Brown bought apples more than one time so there are repeats of names and what they bought, and you are only interested in the first time they bought apples. Thanks!