*The tutorial shows how to lookup with multiple criteria in Excel using INDEX and MATCH and a few other ways.*

Although Microsoft Excel provides 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. This tutorial explains the syntax and inner mechanics in full detail so that you can easily adjust the formula for your particular needs. To make the examples easier to follow, you are welcome to download our sample workbook.

When working with large databases, you may sometimes find yourself in a situation when you need to find something but don't have a unique identifier for the search. In this case, lookup with several conditions is the only solution.

To look up a value based on multiple criteria in separate columns, use this generic formula:

{=INDEX(*return_range*, MATCH(1, (*criteria1*=*range1*) * (*criteria2*=*range2*) * (…), 0))}

Where:

*Return_range*is the range from which to return a value.*Criteria1*,*criteria2*, … are the conditions to be met.*Range1*,*range2*, … are the ranges on which the corresponding criteria should be tested.

The formula is an advanced version of the iconic INDEX MATCH that returns a match based on a single criterion. To evaluate multiple criteria, we use the multiplication operation that works as the AND operator in array formulas. Below, you will find a real-life example and the detailed explanation of the logic.

For this example, we will be using a table in the so-called "flat-file" format with each separate criteria combination (region-month-item in our case) on its own row. Our goal is to retrieve the sales figure for a certain item in a specific region and month.

With the source data and criteria in the following cells:

*Return_range*(sales) - D2:D13*Criteria1*(target region) - G1*Criteria2*(target month) - G2*Criteria3*(target item) - G3*Range1*(regions) - A2:A13*Range2*(months) - B2:B13*Range3*(items) - C2:C13

The formula takes the following shape:

`=INDEX(D2:D13, MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))`

Enter the formula, say in G4, complete it by pressing Ctrl + Shift + Enter and you will get the following result:

The trickiest part is the MATCH function, so let's figure it out first:

`MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))`

As you may remember, MATCH(lookup_value, lookup_array, [match_type]) searches for the lookup value in the lookup array and returns the relative position of that value in the array.

In our formula, the arguments are as follows:

*Lookup_value*: 1*Lookup_array*: (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13)*Match_type*: 0

The 1^{st} argument is crystal clear - the function searches for the number 1. The 3^{rd} argument set to 0 means an "exact match", i.e. the formula returns the first found value that is exactly equal to the lookup value.

The question is - why do we search for "1"? To get the answer, let's have a closer look at the lookup array where we compare each criterion against the corresponding range: the target region in G1 against all regions (A2:A13), the target month in G2 against all months (B2:B13) and the target item in G3 against all items (C2:C13). An intermediate result is 3 arrays of TRUE and FALSE where TRUE represents values that meet the tested condition. To visualize this, you can select the individual expressions in the formula and press the F9 key to see what each expression evaluates to:

The multiplication operation transforms the TRUE and FALSE values into 1's and 0's, respectively:

`{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}`

And because multiplying by 0 always gives 0, the resulting array has 1's only in the rows that meet all the criteria:

`{0;0;1;0;0;0;0;0;0;0;0;0}`

The above array goes to the *lookup_array* argument of MATCH. With *lookup_value* of 1, the function returns the relative position of the row for which all the criteria are TRUE (row 3 in our case). If there are several 1's in the array, the position of the first one is returned.

The number returned by MATCH goes directly to the *row_num* argument of the INDEX(array, row_num, [column_num]) function:

`=INDEX(D2:D13, 3)`

And it yields a result of $115, which is the 3^{rd} value in the D2:D13 array.

The array formula discussed in the previous example works nice for experienced users. But if you are building a formula for someone else and that someone does not know array functions, they may inadvertently break it. For example, a user may click your formula to examine it, and then press Enter instead of Ctrl + Shift + Enter. In such cases, it would be wise to avoid arrays and use a regular formula that is more bulletproof:

INDEX(*return_range*, MATCH(1, INDEX((*criteria1*=*range1*) * (*criteria2*=*range2*) * (..), 0, 1), 0))

For our sample dataset, the formula goes as follows:

`=INDEX(D2:D13, MATCH(1, INDEX((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0, 1), 0))`

As the INDEX function can process arrays natively, we add another INDEX to handle the array of 1's and 0's that is created by multiplying two or more TRUE/FALSE arrays. The second INDEX is configured with 0 *row_num* argument for the formula to return the entire column array rather than a single value. Since it's a one-column array anyway, we can safely supply 1 for *column_num*:

`INDEX({0;0;1;0;0;0;0;0;0;0;0;0}, 0, 1) returns {0;0;1;0;0;0;0;0;0;0;0;0}`

This array is passed to the MATCH function:

`MATCH(1, {0;0;1;0;0;0;0;0;0;0;0;0}, 0)`

MATCH finds the row number for which all the criteria are TRUE (more precisely, the the relative position of that row in the specified array) and passes that number to the *row_num* argument of the first INDEX:

`=INDEX(D2:D13, 3)`

This example shows how to perform lookup by testing two or more criteria in rows and columns. In fact, it's a more complex case of the so-called "matrix lookup" or "two-way lookup" with more than one header row.

Here's the generic INDEX MATCH formula with multiple criteria in rows and columns:

{=INDEX(*table_array*, MATCH(*vlookup_value*, *lookup_column*, 0), MATCH(*hlookup_value1* & *hlookup_value2*, *lookup_row1* & *lookup_row2*, 0))}

Where:

*Table_array* - the map or area to search within, i.e. all data values excluding column and rows headers.

*Vlookup_value* - the value you are looking for vertically in a column.

*Lookup_column* - the column range to search in, usually the row headers.

*Hlookup_value1, hlookup_value2, … * - the values you are looking for horizontally in rows.

*Lookup_row1, lookup_row2, … *- the row ranges to search in, usually the column headers.

It is a variation of the classic two-way lookup formula that searches for a value at the intersection of a certain row and column. The difference is that you concatenate several hlookup values and ranges to evaluate multiple column headers. To better understand the logic, please consider the following example.

In the sample table below, we'll be searching for a value based on the row headers (Items) and 2 column headers (Regions and Vendors). To make the formula easier to build, let's first define all the criteria and ranges:

*Table_array*- B3:E4*Vlookup_value*(target item) - H1*Lookup_column*(Row headers: items) - A3:A4*Hlookup_value1*(target region) - H2*Hlookup_value2*(target vendor) - H3*Lookup_row1*(Column headers 1: regions) - B1:E1*Lookup_row2*(Column headers 2: vendors) - B2:E2

And now, supply the arguments into the generic formula explained above, and you will get this result:

`=INDEX(B3:E5, MATCH(H1,A3:A5,0), MATCH(H2&H3,B1:E1&B2:E2,0))`

Remember to complete the formula by pressing the Ctrl + Shift + Enter shortcut, and your matrix lookup with multiple criteria will be done successfully:

As we are searching vertically and horizontally, we need to supply both the row and column numbers for the INDEX(array, row_num, column_num) function.

*Row_num* is delivered by MATCH(H1, A3:A5, 0) that compares the target item (Apples) in H1 against the row headers in A3:A5. This gives a result of 1 because "Apples" is the 1st item in the specified range.

*Column_num* is worked out by concatenating 2 lookup values and 2 lookup arrays: MATCH(H2&H3, B1:E1&B2:E2, 0))

The key factor for success is that the lookup values should match the column headers exactly and be concatenated in the same order. To visualize this, select the first two arguments in the MATCH formula, press F9, and you will see what each argument evaluates to:

`MATCH("NorthVendor 2", {"NorthVendor 1", "NorthVendor 2", "SouthVendor 1", "SouthVendor 2"}, 0)`

As "NorthVendor 2" is the second element in the array, the function returns 2.

At this point, our lengthy two-dimensional INDEX MATCH formula transforms into this simple one:

`=INDEX(B3:E5, 1, 2)`

And returns a value at the intersection of the 1st row and 2nd column in the range B3:E5, which is the value in the cell C3.

That's how to look up multiple criteria in Excel. I thank you for reading and hope to see you on our blog next week!

Excel INDEX MATCH multiple criteria (.xlsx file)

- VLOOKUP with multiple criteria – this requires adding a helper column to the left of your table and concatenating all criteria in that column. That's not a very elegant solution, but you may want to know all available options.
- XLOOKUP with multiple criteria – this recent addition to the family of Excel's lookup and reference functions (currently available only to Office 365 subscribers) handles arrays by design. Meaning, it works as a regular formula, not an array formula!
- Lookup multiple criteria and return multiple results - combine 5 different functions in a single formula to evaluate several criteria and return all matches either in a column or row.
- Merge Tables Wizard - visual way to Vlookup in Excel with one or more criteria.

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Shared email templates

Category: Excel Tips and How-to

## 107 responses to "Excel INDEX MATCH with multiple criteria - formula examples"

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.

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

Thank you!

This is an excellent explanation. Thank you so much!!

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.

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

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.

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.

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.

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.

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.

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

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

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.

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

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.

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

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.

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

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.

Thank you for this article- you helped me solve my problem!

Thank you so much u solved my 4months doubts

Everything is competent and affordable!

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

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.

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

Hi, I have a query. I have a data matrix and some of the cells are filled with Y, where the Row header and Column header combination is active. For every value Y, I need to perform a vlookup in another sheet using the corresponding row and column header values. In the example below, I need to replace the Ys with the number from 2nd sheet. Any suggestions and help is appreciated.

SHeet 1 -

AA BB CC DD

A Y

B Y

C Y

D Y

Sheet 2 -

A AA 1

B BB 2

C CC 3

D DD 4

SHeet 1 is :

AA BB CC DD

A Y

B Y

C Y

D Y

I'm looking for something that gives me joined text.

List is like 1) Name 2)Work Done 3)Amount 4)Month

Output list is like

1)Month Name

2)Sum of Amount for Month Given

3)Textjoin of Workdone for Month Given

Any help ??

use textjoint formula for this and textjoint formula avilable on msoffice 365 or u can use some vba codes for this also .....for more about it send me excel file on my mail...

I'm developing a 15 x 15 matrix of results, and looking to return the column and row numbers for the MAX result in the 15 x 15 range ... any suggestions?

Hello!

If your array of values is located in the range A1: O15, then the row number with the maximum value can be found by array formula

=MIN(IF(A1:O15=MAX(A1:O15),ROW(A1:A15)))

column number -

=MIN(IF(A1:O15=MAX(A1:O15),COLUMN(A1:O1)))

This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter.

Thanks for the advice ... really helped me out

dear Sir/Mam,

i want to know if data is in 3 cell & data value is also in 3 same down side cell i want find data which is in three cell and give result sum of these data value...

data 16 18 20 22 24 26

value 240 240 240 240 240 240

data 16-18-20

sum 720

Hello!

If I understand your task correctly, the following formula should work for you:

=IF(SUM(--(A1:C1<>""))=3,SUM(A2:C2),"")

Hope this is what you need.

NO SIR ITS NOT WORKING WHICH I LIKE THIS TYPE....

COLOUM DATA 1 2 3 16 18 20 22 24

SUM VALUE 50 500 120 240 240 240

COLOUM DATA SUM VALUE

1-2-3 670

2-3-16 860

3-18-20 600

Hello!

The formula I sent to you was created based on the description you provided in your first request. Your explanation is completely incomprehensible. What does 1-2-3 mean? Is it the value of one cell or several?

I will no longer guess what you need. In order to prevent it from happening, please provide me with the detailed description of your task.

COLOUM DATA | 1 | 2 | 3 | 16 | 18 | 20 | 22 | 24

VALUE |50| 500|120| 240|240 | 240|100|200

COLOUM DATA SUM VALUE

1-2-3 670

2-3-16 860

3-18-20 600

1-2 550

16 240

sir coloum data value which is in separate separate cell and in this cell canted only numarical value

i want when i write two or three numerical value in single cell than ans we get sum of this data value......pls help me...

What does 1-2-3 mean? Is it the value of one cell or several?....

sir ji 1-2-3 means its value of several cell....which is in one cell and i need only sum of its corresponding value ...

Hi, i need some help using the below template formula:

=INDEX(B3:E5, MATCH(H1,A3:A5,0), MATCH(H2&H3,B1:E1&B2:E2,0))

it only returns one data instead of listing the rows with the same criterias met

Below is my data example:

*Sheet 1*(Raw data)-

Column A: List of Coach Names (Not Unique)

Column B: Student Name

Column C: Tuition Day

Column D: Tuition Time

*Sheet 2*-

Cell A1: Coach Name (e.g. Tom)

Cell A2: Tuition Day (e.g. Monday)

Cell A3: Tuition Time (e.g. 2pm)

Cell B1(Column B): Formula Output list of cells in column B that match criterias.

Criteria is- Show and list all student name if below 3 criterias are met:

1) Column A match with cell A1 (Coach Name)

2) Column C match with cell A2 (Tuition Day)

3) Column D match with cell A3 (Tuition Time)

In Sheet 2: cell A1, A2, A3, the content of the cell is changed daily manually.

I want Sheet 2: cell B1 column B to use formula to list all student names that match the criterias.

Really appreciate any assistance and insights! :)

Hello!

I recommend that you read the guide on how to find many matches by several criteria and display these matches in the table.

Hope this is what you need.

I am looking to do something that is a bit of an extension to this. I have a round-robin league set up with 5 columns -- a match number and four player name columns. Any player name could be in any of the four columns for any given match. I want to be able to find the match number (or numbers) when one to four players are entered as search criteria. I think the Index-Match approach will work but I need to return an array of match numbers (which may have one or more values) and the matching has to have logic to exclude blank search criteria fields.

Match# Player_1 Player_2 Player_3 Player_4

1 Tom Tim Steve John

2 Mary Alan Jim Tom

3 Alan John Mark Ed

4 Jim Mary Tim Alan

Search_1:

Search_2:

Search_3:

Search_4:

Return Array:

Match #s

Example 1:

Search_1: Alan

Return Array:

2

3

4

Example 2:

Search_1: John

Search_2: Alan

Return Array:

3

Example 3:

Search_1: Mary

Search_2: Alan

Search_3: Jim

Return Array:

2

4

Thanks for any insight on an approach.

There are 2 sheets in which one column (alpha numeric value) is same in both the cases. The two sheets to be compared and give the value in column if both the cases are matching. The other column data to be called.

Please help.

Hello!

We have a tool that can solve your task in a couple of clicks: Ablebits Data - Compare Sheets.

This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial

What does 1-2-3 mean ? Is it the value of one cell or several ?....

sir ji 1-2-3 means its value of several cell....which is in one cell and i need only sum of its corresponding value ...

Hi,

I have data like this:

Col B Col C Col D Col E

1 Project 12-Oct 19-Oct 26-Oct

2 12345 START MIDDLE FINISH

3 78989 MIDDLE FINISH TEST

In B15 I want to have the current status of project to show based on the current date (B14 has the current Monday).

How can I use index match for that?

Thanks,

I wonder if it's possible to use the criteria from one sheet to search for information on a second chart.

I want to see if the annual salary for each job title falls in the min, mid, or max range for each job level.

Every I try gets errors.

Chart 1

JOB TITLE JOB LEVEL ANNUAL SALARY MARKET-RATIO

Analytics Developer Junior $60,007.00

Analytics Developer Lead $95,009.00

Automation Tester Senior $95,009.00

Business Analyst Intermediate $95,009.00

Content Writer Senior $64,018.00

Visual Designer Senior $110,016.00

Visual Designer Senior $95,009.00

Chart 2

Job Title Level Min Mid Max

Analytics Developer Junior 60,000 75,000 90,000

Intermediate 70,000 87,000 104,000

Senior 82,000 103,000 124,000

Automation Tester Junior 56,000 70,000 84,000

Intermediate 70,000 87,000 104,000

Senior 79,000 99,000 119,000

Business Analyst Junior 56,000 70,000 84,000

Intermediate 68,000 85,000 102,000

Senior 85,000 106,000 127,000

Content Writer Junior 54,000 68,000 82,000

Intermediate 64,000 80,000 96,000

Senior 80,000 100,000 120,000

Visual Designer Junior 56,000 70,000 84,000

Intermediate 68,000 85,000 102,000

Senior 85,000 106,000 127,000

Hello!

Please specify, what formula you used and what problem or error occurred.

It’ll help me understand it better and find a solution for you.

hi, thank you for the great explanations!!

hopefully you could solve my problem:

i have a table of different plants varieties (column a) and the flowering date (column b) for each variety, i have another table with the height of the plant varieties measured daily (the first column is the list of the varieties and each date of measuring the height is a separate column). i would like to find the height of each variety at its flowering date. i.e find the match between the flowering date and return the height measured at this date

thanks

Hello!

I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

If you cannot solve the problem yourself, 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.

Hello,

I have a list of products and it has multiple date. I required all the date against the product one by one.

when i used the below formula i got the smallest date (1/31/2019) in the list against one of the product. and still there are more 11 dates i should get. but when i copied this formula in the next column its showing an error.

=INDEX('Evaluation data'!$W$2:$W$12277, SMALL(IF($A2='Evaluation data'!$M$2:$M$12277, ROW('Evaluation data'!$M$2:$M$12277)-ROW('Evaluation data'!$M$2)+1), COLUMN(A:A)))

Colum M Colum W

TYRE 1 1/31/2019

TYRE 1 1/31/2019

TYRE 1 4/10/2019

TYRE 1 4/10/2019

TYRE 1 5/3/2019

TYRE 1 5/3/2019

TYRE 1 5/3/2019

TYRE 1 5/3/2019

TYRE 1 6/11/2019

TYRE 1 6/11/2019

TYRE 1 7/10/2019

TYRE 1 8/15/2019

TYRE 1 9/13/2019

TYRE 1 5/25/2020

Hello!

It is very difficult to understand a formula that contains unique references to your workbook worksheets.

Please check out this article to learn how to vlookup multiple matches in Excel with one or more criteria.

I hope my advice will help you solve your task.

Hello Alexander,

This formula is for index match for multiple criteria has really helped me a lot. Thank you! My question is, if all the criteria has been fulfilled but I want the value below that cell to be reflected, how would the formula look like?

This is a sample formula that I use, when there's just one criteria:

=INDEX(Sheet39!B:B,MATCH(A3,Sheet39!B:B,0)+2,1)

And here's an example of the formula with multiple criteria that I would like to reference a cell below:

=IFERROR(INDEX(Sheet39!B:B,MATCH(1,(Sheet39!D:D=B$3)*(Sheet39!A:A=$A$1),0)),"")

Thank you in advance!

Hello!

Unfortunately, without seeing your data it is difficult to give you any advice.

Your formula should follow the guidelines in this section.

Hello Alexander,

Apologies for not being clearer, please see sample below:

NAME AGE PLACE

ADAM 10 LA

BRODY 11 LA

CHARLIE 12 LA

ADAM 8 CA

LUKE 9 CA

CHARLES. 10 CA

if I want to see the result for who is the next to ADAM in CA for example (as there are many Adams in different places), but it's important to see the one who is listed next to him (cell below), I am trying to use =INDEX(A:A,MATCH(A4,A:A,0)+1,1) where i typed ADAM on A4. The result here is BRODY.

So if I want "LUKE" to show, I will then have to create multiple criterias for Adam, to include, say the AGE or PLACE. And unfortunately I am unable to do it with this formula for multiple criterias: =IFERROR(INDEX(A:A,MATCH(1,(A:A=A4)*(C:C=CA),0)),""). I can't seem to place the +1 or +2 cell below to show the result of LUKE or CHARLES.

Thank you in advance for your assistance!

Hello!

If I got you right, the formula below will help you with your task:

=IFERROR(INDEX(A:A,MATCH(1,(A:A=A4) * (C:C="CA"),0)+1),"")

I hope I answered your question. If something is still unclear, please feel free to ask.

Thank you Alexander for the assistance. Really appreciate it. Have a good day and keep safe!

Hello Alexander,

Please help, i am stuck at a report which can be understood from below table eg:

i need to index marks in similar table with only those students names who have marks less than 40, along with marks in similar column but only less than 40. I am not sure which function to apply but trying

index only without success so far.

Student Math History English Science

A 35 70 85 20

B 55 64 30 81

C 47 49 40 79

D 62 52 94 27

E 15 35 50 32

F 38 75 29 19

Thanx in advance

Hello!

If I got you right, please check out this article to learn how to VLOOKUP multiple values in Excel with one or more criteria.

To help you write your formula, describe in detail the result you would like to get from your data.

Thanks for your help Alexander, the result i would want is some thing like below table, since

only those marks are shown which are less than 40 (i have put - to represent blanks), and only those student names should appear

whose marks are less than 40, like in below table name of student C is not there. ( I think in vlookup we have to put all the names)

Student Math History English Science

A 35 - - 20

B - - 30 -

D - - - 27

E 15 35 - 32

F 38 - 29 19

Thanks in advance and apologies to trouble you again.

I'm having issues with Index + Match working correctly. Here's what I am trying to do:

> find a Number in a row based on two separate search criteria, each in its own row:

Criteria 1 is to find a specific Date in row range B1:G1

Criteria 2 is to find a specific Word in row range B2:G2

The Number I need to find is in the Column of row 3 where Criteria 1& 2 are matched.

Thanks in advance.

Your example file doesn't work lol - there is #Value! in cell with result if i push ENTER to recalculate your formula.

Hello George,

If it's an array formula, then you should press Ctrl + Shift + Enter to recalculate it. I've just checked all the examples in our sample workbook and all 3 formulas recalculated just fine.

If the error persists on your side, please let me know which example does not work and what Excel version you are using.

Hi, I want to search a cell range for a string and place the matching cells into a new column.

For example,

In Cells A1:A5 below... Column P

AAL Jan 08 2021 18.5 Call AAL Jan 08 2021 18.5 Call

CBD DENVER INC (CBDD) DAL Dec 31 2020 42.0 Call

DAL Dec 31 2020 42.0 Call

FCEL Dec 18 2020 8.0 Put

SANUWAVE HEALTH INC (SNWV)

I want to find all the "Call" strings and place them sequentially in column P (shown above).

How can I use the index and if macros to do this? Or how would you do this?

Thanks!

Hello,

Is there any reason why the function below will not work for "INDEX MATCH with several criteria - formula example"? I still get $115 when I use the formula below (without pressing Ctrl + Shift + Enter).

=INDEX(D2:D13,MATCH(G1&G2&G3,A2:A13&B2:B13&C2:C13,0))

Thank you!

Hi,

Unfortunately, without seeing your data it is difficult to give you any advice.

Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

Hi Alexander,

Appreciate the reply! I was referring to the first example shown on this page.

The first formula is what Svetlana shows to get to the answer, but I was wondering why an array formula needs to be used if the second formula also gets me to the correct answer.

={INDEX(D2:D13, MATCH(1, (G1=A2:A13)*(G2=B2:B13)*(G3=C2:C13), 0))}

=INDEX(D2:D13,MATCH(G1&G2&G3,A2:A13&B2:B13&C2:C13,0))

Thanks,

Scott

Hello!

Two solutions are always better than one. You can use any formula. However, Microsoft warns that the string concatenation operation is computationally intensive. Therefore your second formula with a lot of data will be slower than the others.

Appreciate the help!

HI,

Can someone help me to add INDEX(Data!A1:P1,MATCH(J14,Data!A1:J1,0)) formula to COUNTIFS(Data!A2:A4000,H8,Data!B2:B4000,I8,Data!C2:C4000,J8,Data!F2:F4000,">0"). I am trying to count numerical cells where Header and column Criteria is being matched.

Thank You

Hello!

The COUNTIFS function uses only range references as criteria_range. Therefore, you cannot use the INDEX function for this. So that I can give you advice on how to write a different formula, please describe your problem in more detail.

Thank you for prompt reply.

Data Sheet

HDR-1 HDR-2 HDR-3 HDR-4 HDR-5 HDR-6

ST BG DC 60 NA 35

GT CG DB 20 40 40

ST BG DC NA 60 90

ST CG DB 30 20 NA

ET BG DC 30 20 20

ST BG DC 50 10 10

Below Cell Values need to be Match in columns and header of Data sheet

ST BG DC HDR-4

HDR-4 Count Result = 2

Hi,

I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

=SUM(IFERROR((A2:A10=$H$8)*(B2:B10=$I$8)*(C2:C10=$J$8) * (ISNUMBER(INDIRECT(ADDRESS(2,MATCH($J$14,A1:J1,0))&":"&ADDRESS(2,MATCH($J$14,A1:J1,0))&"00"))),0))

$J$14 -- HDR-4

H8:J8 -- ST BG DC

I hope it’ll be helpful.

Thanks Alexander for the answer, unfortunately, this formula doesn't work for me. I will try to explain this issue in more detail. Suppose I have data from A2 to J10 and A1 to J1 is the data header (HRD-1, HRD-2....HRD-10 ).

Now I will describe the criteria.

Assume that 1st Criteria is in cell H13 to J13, as stated in formula (A2:A10=$H$13)*(B2:B10=$I$13)*(C2:C10=$J$13). And 2nd criteria is in J14, as stated in the formula MATCH($J$14,A1:J1,0).

In K14 I want to count the total number of numeric data of each column (HD-4 column {i.e D2:D10

}...... to........ HD-10 column {i.e J2:J10}) by keep changing cell criteria in H13 to J13 as well as the header criteria in cell J14.

Hope you understand my description.

can reverse back the formula?

which mean I put some price, I know the product from which region.

This formula worked for me.

SUM(COUNTIFS($A$2:$A$10,H13,$B$2:$B$10,I13,$C$2:$C$10,J13,INDEX($D$2:$J$10,,MATCH($J$14,$D$1:$J$1,0)),{"=0","**"}))

Hi, thank you for explaining the Index/match function.

I get the formula to work outside of 'tables', but not between tables.

Formula is written like: {=Index(Cardata[emissions];Match(1;(Form[fueltype]=Cardata[fueltype])*(Form[cartype]=Cardata[cartype]);0))}

It should return a corresponding emission number.

***Is there another way to make this formula work between tables?***

Much appreciated!

Rolf

Hello!

Unfortunately, without seeing your data it is difficult to give you any advice.

Is your formula not working? Please describe the problem in more detail.

Hi, I have 2 columns that has Performance rating of 2 years. Say,

Column A = 2 Column B =1 column C: an amount 3000 This will be in different combinations like, 1,2, 1,3, 2,1 etc for different employees

I need to calculate :

If column a=1, column b=2, then it should fetch Column C *2+200

If column a=2, column c=3, then it should fetch column C* 0.5+ 300

Similarly, different combinations of ratings for 2 years should fetch a value where different formulas are applied. How do I do that?

Hello!

Here is the article that may be helpful to you:

Nested IF formulas and

New Excel IFS function instead of multiple IF

I hope it’ll be helpful.

Excellent explanation and detail - thank you for posting!

I am trying to get a value returned based on a table of information with ranges. Below is the table I am working with. It is the bags requirements for a house foundation based on the square footage of the house slab, and the average height of the slab. I want to be able to return a value based on the two separate inputs and return the correct row and column.

Example: the house slab is 3073 sf (this is B7 in the spreadsheet) and the average height is 4.08 ft (this is B12 in the spreadsheet). That should come out to be 15 (third column, fourth row).

Height 2000-2500 2500-3000 3000-3500 3500-4000 4000-4500 4500-5000 5000-5500

1.0-2.0 8 9 12 14 15 16 17

2.0-3.0 9 11 13 15 16 17 19

3.0-4.0 10 13 14 15 17 19 20

4.0-5.0 11 14 15 16 19 20 22

5.0-6.0 12 15 16 17 20 22 22

6.0-7.0 13 16 17 18 22 22 23

7.0-8.0 15 17 18 19 22 23 24

8.0-9.0 16 18 19 19 23 24 26

9.0-10.0 17 19 20 20 24 26 26

10.0-11.0 18 20 21 22 26 26 27

11.0-12.0 19 21 22 22 26 27 28

Note: The table in my worksheet spans G24:N35

The problem I am getting into is that I don't have specific values I am checking for along the rows and columns, but ranges in both. The current formula I have is below, but I am getting a #VALUE! error due to the 4.08 average height getting missed in my formula. Is there a better way to format this formula to manage all the ranges of the two values I am needing to check in the table or am I really stuck with all the nested IF's?

=@INDEX(H25:N35,IF(B12=2.1,B12=3.1,B12=4.1,B12=5.1,B12=6.1,B12=7.1,B12=8.1,B12=9.1,B12=10.1,B12=11.1,B12<12.1),11,0))))))))))),IF(B7=2501,B7=3001,B7=3501,B7=4001,B7=4501,B7=5001,B7<5501),7))))))))

Hmm, the comment formatting broke my formula...that's not what it's suppose to read as. Maybe this will work.

=@INDEX(H25:N35, IF(B12=2.1 , B12=3.1 , B12=4.1 , B12=5.1 , B12=6.1 , B12=7.1 , B12=8.1 , B12=9.1 , B12=10.1 , B12=11.1 , B12<12.1) , 11 , 0))))))))))) , IF(B7=2501 , B7=3001 , B7=3501 , B7=4001 , B7=4501 , B7=5001 , B7<5501) , 7))))))))

Hello!

Write the first line as 2000 2500 3000 etc.

Write the first column as 1 2 3 4 5, etc. If 4.08 is written in I1, in 3072 it is written in I2, then you can use the formula

=VLOOKUP(I1, A2:H12, MATCH(I2, A1:H1, 1), 1)

Please check out this article to learn how to Vlookup based on row and column values.

I hope I answered your question. If something is still unclear, please feel free to ask.

Much cleaner formula. Works perfectly, thanks!

Stupid web codes...

=@INDEX(H25:N35,IF(B12 < 2.1,1,IF(AND(B12 > =2.1,B12 < 3.1),2,IF(AND(B12 > =3.1,B12 < =4),3,IF(AND(B12 > =4.1,B12 < 5.1),4,IF(AND(B12 > =5.1,B12 < 6.1),5,IF(AND(B12 > =6.1,B12 < 7.1),6,IF(AND(B12 > =7.1,B12 < 8.1),7,IF(AND(B12 > =8.1,B12 < 9.1),8,IF(AND(B12 > =9.1,B12 < 10.1),9,IF(AND(B12 > =10.1,B12 < 11.1),10,IF(AND(B12 > =11.1,B12 < 12.1),11,0))))))))))),IF(B7 < 2501,1,IF(AND(B7 > =2501,B7 < 3001),2,IF(AND(B7 > =3001,B7 < 3501),3,IF(AND(B7 > =3501,B7 < 4001),4,IF(AND(B7 > =4001,B7 < 4501),5,IF(AND(B7 > =4501,B7 < 5001),6,IF(AND(B7 > =5001,B7 < 5501),7))))))))

It is very nice to have this article.

If I have two excel/csv files, can I make a new file to pull the data to it? (the criteria is in A file, the data is in B file)

Thanks.

Hi,

With the INDEX + MATCH functions, you can extract data using multiple files. In this case, you need to use external references.

In this case, all files must be open in Excel.