Although Microsoft Excel has special functions for vertical and horizontal lookup, expert users normally replace them with INDEX MATCH, which is superior to VLOOKUP and HLOOKUP in many ways. Among other things, it can look up two or more criteria in columns and rows. Continue reading
Comments on: Excel INDEX MATCH with multiple criteria - formula examples
by Svetlana Cheusheva, updated on
Comments page 6. Total comments: 293
Hi
If all the information comes from one Column A & I have three criteria's by way of Race1 to Race2 & Numbers? Across 10 column with information eg:
RACE 1
A B C D E F G H I J
No Last 10 Horse Trainer Jockey Barrier Weight Penalty Hcp Rating
1
2
3
RACE 2
My question how to retrieve 1, 2, 3, & so on & all information across 10 columns?
Regards
Tony
Dear Sir,
I have to pull the districts name having 0 value in column1 and column2 both from data of 4000 rows.
Can we use Index, match formula to pull the district name. If yes pl. describe it.
If not pl. describe, how we can do this.
Pl. tell the simplest way to accomplish this task.
Regards.
Hello!
Unfortunately, without seeing your data it hard to give you advice.
Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you.
What If I have multiple output upon multiple criteria? Then how this formula would be?
Hello!
Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.
Everything is competent and affordable!
Thank you!
Thank you so much u solved my 4months doubts
Thank you for this article- you helped me solve my problem!
To test it, I created the same exact table used the exact same formula (copied and pasted it) and did not work. Formula used Non-Array, got an #N/A.
=INDEX(D2:D13, MATCH(1, INDEX((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0, 1), 0))
Hello!
I hope you have studied the recommendations in the above tutorial. This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter.
Hello! Thanks for the great article. Can the Index/Match formula contain a cell value that also contains an index/match formula? I have the following: S2 contains =Index(Rep_ID, Match(A2, OrderNumber,0)) - (rep_ID and OrderNumber are from AcctsList sheet);
T2 contains =Index(SalesRepName, Match(S2,SalesRepID, 0))- (SalesRepName and SalesRepID are from Slsp Sheet).
T2 returns #N/A unless I change S2 to the value of the formula and I don't want to have to create another column to paste values. Is there a way to combine the formulas in T2 so that it will provide the RepName needed?
Extensive search and forum request has yielded no answers.
Any help is greatly appreciated!
Thank you,
Phisaw
Hello!
I can not check the work of formulas, because I do not have your data. But you can try using this formula
=Index(SalesRepName, Match(Index(Rep_ID, Match(A2, OrderNumber,0)),SalesRepID, 0))
I have to apologize. It works just fine with Office 365, but when I try on the work computer which is running Office 2013 I get the error.
I assume the below info are what the references are, correct?
Hello!
Check the sheet names in your workbook and correct the links
Found one.
In some cases, a customer conducts multiple events at a given facility on the same day. We want to just list each customer once so want to remove duplicates if possible. Not sure if I would have to split out the AllData sheet into separate sheets/tables to make it easier.
I really appreciate all the help Alexander.
I have been stuck trying to get cell content based on row and column matches. I have one worksheet that contains base data of 3 columns (A = Dates, B = Facilities, C = Customers). On worksheet 2 we want to display the information in a linear calendar style that includes each day of a year. The dates are across the top row starting at Column B. Column A is a list of facilities. We want to put the customer into the appropriate cell within the correct facility row and under the correct date(s) column(s). I have tried using pivot tables but I just end up with a count of customers and not the actual customer name in the given cell.
In most cases there is only ever just one customer per facility per day. But on occasion one may just have part of a day and another may have the evening portion. In those cases just listing both with a separator ? or a - would serve our purpose.
I am sure there is a way to do this but looking all over the place and asking have not yielded an answer.
Hello!
On Sheet2 in cell B2, write down the formula.
=CONCAT(IFERROR(INDEX(Sheet1!$C$2:$C$20, SMALL(IF(Sheet2!B$1&Sheet2!$A2=Sheet1!$A$2:$A$20&Sheet1!$B$2:$B$20, ROW(Sheet1!$C$2:$C$20)-1), ROW(INDIRECT("1:"&ROWS(Sheet1!$C$2:$C$20)))))&"-",""))
Then copy it down the column.
Sheet1!$C$2:$C$20 - Customers
Sheet1!$A$2:$A$20 - Dates
Sheet1!$B$2:$B$20 - Facilities
Sheet2!B$1 - Date
Sheet1!$A$2:$A$20 - Facilities
If there is anything else I can help you with, please let me know.
Alexander thanks for the help. When I plug in the formula, I get a #NAME? error and the highlighted in red and blue part of the formula is this part Sheet2!B$1&Sheet2!$A2
how can we make an index formula for
in one column I want a specific text from it
in the second column also I want a different specific text
from the third column it is numbers and we want to put a formula of largest number
fourth column in index column
A b c d
apple delhi 15 abc Ltd
benana chenni 20 x ltd
grape delhi 25 abc ltd
apple mumbai 40 y ltd
benana pune 30 x Ltd
example index delhi, with specification of "apple" "abc Ltd" with smallest number a sper column c
please reply sir
Hello!
I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? Is your data written in column A? Explain exactly what results you want to get from your data?
Write the intended result.
Hi There,
I am trying to create a dynamic income statement in excel, i have attached the link for my spread sheet here
in task 2 i have a list of columns with calculations, in task 3 in the revenue column i wrote =INDEX(Total_ revenue,MATCH(1,(Company_name=C6)*(years=D11),0)) , i thnik the problem is that company_name is a name field with data validation and i need to write another formula connecting it to the revenue column, any ideas , i would vey much appreciate your help
Thank you very much
Jamuna
Hello,
If i want to allocated date by month and with multiple criteria, should i use index match only or add "if" formula?
Hello Anggit!
I’m sorry but your task is not entirely clear to me.
Please describe your problem in more detail. Write an example of the source data and the result you want to get. It’ll help me understand it better and find a solution for you. Thank you.
This was an amazing explanation! I was stuck for a day trying to figure out how to return a cell value (year) based on the max value of a City's population. This did the trick for me! I used a nested: =IFNA(INDEX([year column], MATCH(1,(MAXIFS(..)= [population column])*(@city_name = [Cities Column]),0)),"NOT FOUND"). Thank you so much for taking the time to write this. You taught me something new!!
Great article - thanks.
In your experience, which is the fastest/most efficient approach when there is a lot of data?
Hello Tom,
In my experience, when working with huge bulks of data VLOOKUP is the slowest and most problematic one. So, I'd rely on either INDEX MATCH (works in any version) or XLOOKUP (works in Excel 365 only).
Hi and thank you for such a wonderful post.
I am unable to understand how the formula has returned 3 from below expression.
{1;1;1;1;1;1;0;0;0;0;0;0} * {0;0;1;1;0;0;0;0;1;1;0;0} * {1;0;1;0;1;0;1;0;1;0;1;0}
Appreciate your help.
Hello Enan!
If you perform mathematical operations with the logical values TRUE and FALSE, then Excel turns them into numbers 1 and 0.
I hope I answered your question.
Dear Alexander, Thank you for your response.
i am unable to understand. I want to know how this 3 comes as shown below.
=INDEX(D2:D13, 3)
What i understood is:
={1;1;1;1;1;1;0;0;0;0;0;0} * {0;0;1;1;0;0;0;0;1;1;0;0} * {1;0;1;0;1;0;1;0;1;0;1;0}
={1}*{1}*{1}
=1
Then how come it is coming 3.
Appreciate your help.
In the comment just sent, the NOT EQUAL sign "" is deleted during the posting. I do not know why though. It should be
$N$1:$N$12000, $N$1:$N$12006, $N$1:$N$120044.
Hello?
I am using the following formula to see what numbers (fill color code) are in column N.
=INDEX($N$1:$N$1200,MATCH(N(TRUE),INDEX(($N$1:$N$12000)*($N$1:$N$12006)*($N$1:$N$120044),0,1),0))
I just want to change the number of the criteria in the inner INDEX function depending on the number of the found numbers, e.g. 3 in the above formula. It can be 3, 4, ...
.
Any way to solve the problem?
Many thanks in advance.
Hello,
I have a chart with many names which are duplicates in A and the status of the file (Complete or Incomplete) in C. I wanted the names that showed up more than once which had Incomplete on one day and Complete on the other. So I did that and by using this formula: =IF(AND(COUNTIFS(A:A,A2,C:C,"complete"),COUNTIFS(A:A,A2,C:C,"incomplete")),A2,"")
Because the above formula needed to be dragged down and repeats the names I wanted, I wanted another list that shows me the names generated by the above formula once in a nice neat order - one after the other. Someone gave me this array formula which worked perfectly: =IFERROR(INDEX($F$2:$F$21,MATCH(0,COUNTIF($G$1:$G1,$F$2:$F$21),0)),"")
What I am hoping to get help with is, that countif shows me {1;1;0;1;1;0;1;0;0;1;1;1;0;1;1;0;1;0;0;1} and every 0 is the name that I want generated by the previous formula. The 0 in lookup_value matches that name and the 0 exact match gives it to me. What I don't understand after reading above is, does 0 mean false or true? In the above all the true were 1 and false 0. Why is 0 false when 0 gives me the name I want? Unless countif 0=true and 1=false. Also, I don't understand why the range is in G1 - the signiface.
This formula starts at F2: =IF(AND(COUNTIFS(A:A,A2,C:C,"complete"),COUNTIFS(A:A,A2,C:C,"incomplete")),A2,"")
This starts at G2: =IFERROR(INDEX($F$2:$F$21,MATCH(0,COUNTIF($G$1:$G2,$F$2:$F$21),0)),"")
If anyone can explain that would be great as i am confused and evaluating formula all the time only helped me to a certain extent.
Thanks!
Hello John!
I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
We'll look into your task and try to help.
Hi,
How do I put in the formula if one of the criteria is in a range of numbers e.g. 20 - 29?
E.g. 1st criteria is age, 2nd criteria is exercise time: 10-15 mins,
so results is if age 20 exercises 12 mins = normal
if age 20 exercises 9 mins = weak
exercises 18 mins = strong, etc
Hello Lynn!
Please use the example above: "INDEX MATCH with several criteria - formula example".
Please enter age in column A, exercise time – in column B, and estimation – in column D.
Fill in the table with all possible age variants and exercise time with the corresponding estimation.
After that, you will search for a necessary row in this table using the recommended formula.
If you still have any questions, I will be happy to help you further.
I have a macro-enabled Excel template. I'm trying to figure out which formula(s) I can include my template in order to achieve the following with a single click of a button:
▪ Search for a column name that exists in a different spreadsheet (same workbook)
▪ Identify the starting and ending cells of the column
▪ Store the start and end cells as a variable
▪. Use the variable as an input array for a SUMPRODUCT() calculation
The column to be identified will change in size, since I will be working with multiple imported data sets, and they are all different. Can this be done without VBA?
1)If= first month(1 sep 2019 to 2 feb 2020) all floors commission 2%
2)If= second month (3 feb 2020 to 29 Feb 2020) floor wise commission
Lower floor - 3%
Middle floor - 3.5%
Higher floor - 4 %
3) if = third month ( 1 March 2020 to 31 march 2021) onwards floor wise commission
Lower floor - 2 %
Middle floor - 2.5%
Higher floor -3%
AND
3 TYPES OF SOURCE(X,Y,Z)
Y SOURCE ELEGIBLE ONLY 2% ALL TYPES OF CONDTION
CAN YOU PLEASE HELP WHICH FORMULA WORKING IN 1 CELL excel...
Hello!
I answered you here
I'm trying to lookup a value in a table where the row and column values are a range of numbers. Then I need the result to be placed in an Excel spreadsheet where the criteria is located and varies from each location. Here's a sample table:
Gr Tgt 6
2-3 1 3 4 6 7
4 2 4 5 7 8
5-6 3 5 6 8 9
7-8 4 6 7 9 10
9-10 5 7 8 10 10
So, by specifying a value in the 1st row and a number in the 1st column I need to capture the intersection. Thnaks
Hello Bill!
If I understand your problem right, you want to specify values from a row and a column to pull a number at the intersection of that row and that column. In this case, you may try the following formula:
=INDEX(A1:F5, MATCH("4",A1:A5,0), MATCH(4,A1:F1,0))
Here "4" is the text we search for in the first column, 4 is a number we search for in the first row. The INDEX function will return a value that is located at the intersection of the row and column where the MATCH function finds "4" and 4.
I hope it will solve your problem. If it is not the result you’d like to get, please describe your task in more detail so that I will be able to understand it and help you better.
This is an excellent explanation. Thank you so much!!
Thank you!
I have a database with detail of receipts and expenses for a number of different customers. Is there a way for me to pull data for a particular customer in date order. I could just filter by Customer but I am trying to create a subsidiary account by customer which would be done in a different sheet.
1/1/19 Fred Jones December Charges 800 dr
1/30/19 Fred Jones January Charges 800 dr
1/30/19 Fred Jones Payment 300 cr
why should not try like this type
INDEX(E3:E14,MATCH(TRUE,COUNTIFS(B3:B14,H2,C3:C14,H3,D3:D14,H4),0))
INDEX(E3:E14,MATCH(1,(--(COUNTIFS(B3:B14,H2,C3:C14,H3,D3:D14,H4))),0))
Hi Kanth,
Thank you for your feedback.
These formulas won't work. The point is that COUNTIFS returns the count of cells for which all the criteria are TRUE, while the MATCH function needs a lookup array (not a count!) in which it can find the row(s) that meet all the criteria and pass the relative position of the first found row to INDEX.