# Check Multiple Criteria with Excel INDEX and MATCH

The INDEX function can return a value from a specific place in a list, and the MATCH function can find something in a list. Use INDEX and MATCH together, for a flexible and powerful lookup formula. It’s similar to a VLOOKUP, but the item that you’re looking for doesn’t have to be in the column at the left.

### Get an Item’s Price with INDEX and MATCH

For example, find “Sweater” in a column B of a price list, and get its price from column C. In the screen shot below, cell A7 has the name of the item that we need a price for – Sweater.

We can enter an INDEX and MATCH formula in cell C7, to get the price for that item:

**=INDEX($C$2:$C$4,MATCH(A7,$B$2:$B$4,0))**

### How the INDEX and MATCH Formula Works

Here’s how that formula finds the sweater price:

- the MATCH function can find “Sweater” in the range B2:B4. The result is 1, because “Sweater” is in the first row of that range.
- the INDEX function can tell you that in the range C2:C4, the first row contains the value 10.

So, by combining INDEX and MATCH, you can find the row with “Sweater” and return the price from that row.

### Find a Match for Multiple Criteria

In the previous example, the match was based on the Item name – Sweater. Sometimes life, and Excel workbooks, are more complicated.

In the screen shot below, each item is listed 3 times in the pricing lookup table. To get the right price, you’ll need to specify both the item name and the size. We want to find the price for a large jacket.

### Does it MATCH? True or False

Instead of a simple MATCH formula, we’ll use one that checks both the Item and Size columns. To do something similar on a worksheet, we could add columns to check the item and size columns.

- If the Item in column B is a Jacket, the result in column E is TRUE. If not, the result is FALSE
- If the Size in column C is Large, the result in column F is TRUE. If not, the result is FALSE

In column G, when you multiply the TRUE/FALSE values, the result is 1, only if both are TRUE.

We could use a MATCH formula to find the position of a 1 in column G, in the screen shot above. The 8th row of data (worksheet row 9), has the 1, and that row will give us the correct price for a large jacket.

### Use MATCH With Multiple Criteria

Instead of adding extra columns to the worksheet, we can use an array-entered INDEX and MATCH formula to do all the work. Here is the formula that we’ll use to get the correct price, and below is the explanation:

**=INDEX($D$2:$D$10,
MATCH(1,(A13=$B$2:$B$10)*(B13=$C$2:$C$10),0))**

NOTE: This is an array-entered formula, so press **Ctrl + Shift + Enter**, instead of just pressing the Enter key.

In this INDEX and MATCH example,

- prices are in cells
**D2:D10**, so that is the range that the INDEX function will use. - item name is in cell
**A13** - size is in cell
**B13**.

The formula checks for the selected items in **$B$2:$B$10**, and sizes in **$C$2:$C$10**. The results are multiplied.

- (
**A13**=**$B$2:$B$10**)*(**B13**=**$C$2:$C$10**)

The MATCH function looks for the **1 **in the array of results.

**MATCH(1,(A13=$B$2:$B$10)*(B13=$C$2:$C$10),0)**

If you select that part of the formula and press the F9 key, you can see the calculated results. In the screen shot below there are 9 results, and all are zero, except the 8th result, which is 1.

So, the INDEX function returns the price – 40 – from the 8th data row in column D (cell D9).

To find the product code for the selected item and size, you would change the formula to look in cells A2:A10, instead of the price column.

**=INDEX($A$2:$A$10,
MATCH(1,(A13=$B$2:$B$10)*(B13=$C$2:$C$10),0))**

In this example, the product code would be JK003, from cell A9.

### More INDEX and MATCH Examples

Learn more about Lookup, Information and Text functions in the 30 Excel Functions in 30 Days kit.

For more tips and examples, and to download the INDEX and MATCH sample file, visit the **INDEX function and MATCH function page** on the Contextures website. This is Example 4 in the sample file section of that page.

_____________

I don’t remember if I first learned about this from you or from Mike Alexander’s BaconBits blog or from Mr.Excel.com, but it is **SO** worth learning to use! Seriously life-changing – lol! (yeah, my co-workers look at me like I’m weird or something…)

@Lynda, you’re right, and your co-workers don’t know what they’re missing!

I use a slightly different technique using the SUMPRODUCT function combined with the ROW function inside the INDEX, then I don’t have to remember to make it an array formula. Your formula in the example, “=INDEX($A$2:$A$10,

MATCH(1,(A13=$B$2:$B$10)*(B13=$C$2:$C$10),0))” would be written as “=INDEX($A$2:$A$10,

SUMPRODUCT(($B$2:$B$10=A13)*($C$2:$C$10=B13),ROW($A$2:$A$10)))”. The assumption is that your multiple criteria will yield a distinct result.

@Mark, SUMPRODUCT doesn’t work if there is more than one record that matches your search criteria.

If the Ctrl+Shift+Enter is an issue, we can modify Debra’s formula to avoid it.

=INDEX($A$2:$A$10,MATCH(1,INDEX((A13=$B$2:$B$10)*(B13=$C$2:$C$10),0),0))

Regards

In cases where there is more than one record that matches the search criteria, can the formula be modified to find the 2nd or 3rd record that matches?

Was a reply posted with regards to

Ryan

October 15, 2012 at 7:55 pm · Reply

In cases where there is more than one record that matches the search criteria, can the formula be modified to find the 2nd or 3rd record that matches?

this is what im needing my spreadsheet to do

Excel’s database function DGET would be perfect for this (not that there’s anything wrong at all with your great Index/Match example). Alas, for once you haven’t posted your data…so I haven’t prepared an example…*sigh*

@Jeff, thanks, and I’ve added a note in the article that mentions where to find the sample data.

It sounds like a VLOOKUP to me when we combine theose INDEX and MATCH. Am I right?

@Maxime, yes it’s similar to a VLOOKUP, but even better. For example, it can find a value to the left of the lookup value, which VLOOKUP can’t do.

@ Debra: You are so right. By the way I was looking at your “FormSheetEditOptDel”. You’re an Excel genius. Bravo!

…and THANK YOU indeed!

@Maxime, thanks!

It’s VLOOKUP on steroids…

The data is as under

emp.no. gc date1 gc1 date1 gc2 date2

22101 4 1-1-99 9 1-1-04 11 1-1-07

2839 9 1-1-95 11 1-1-00 14 1-1-04

empno gc2 date2

2839 14 ??

what formula i can put at question marks so as to arrive at the date viz. 1-1-2004

regards govind

Dear Sir,

I am using a MS excel formula as below ;

=INDEX(table1,MATCH($B9,INDEX(table1,,1),0),MATCH(AJ$5,INDEX(table1,1,),0))

I get answer in return as per the search by this formula.

But in the case where there is 0 (zero) in the answer searched by this formula I want blank

(i.e. ” “) instead of 0 zero)

So what formula should I use.

Please suggest.

Thank you.

Rajan.

you can use IF Function for example =if(INDEX(table1,MATCH($B9,INDEX(table1,,1),0),MATCH(AJ$5,INDEX(table1,1,),0))=0,””,INDEX(table1,MATCH($B9,INDEX(table1,,1),0),MATCH(AJ$5,INDEX(table1,1,),0)) you use this formula you will get your answer..

Rajan…this is best handled by using a custom number format that hides zeros. For instance, apply the following custom format to the results cells:

#,##0; #,##0;

…or this for dollars:

$#,##0; $#,##0

But if your lookup table only contains strings – and not numbers – you can also do it by amending your formula by adding an empty string on to the end using this:

&””

…which turns any numbers to text. i.e. like this:

=INDEX(table1,MATCH($B9,INDEX(table1,,1),0),MATCH(AJ$5,INDEX(table1,1,),0)),0,””)& “”

i have two tables

what i need is to retrieve the matching credit loan number with the following criteria….where credit date should be greater then the debit date….and the corresponding amount should be retrieved….

table 1

LOAN DEBIT DATE AMOUNT

A 1-Apr-2012 10000

B 1-May-2012 30000

C 1-Jun-2012 50000

D 1-Jul-2012 2000

E 1-Mar-2012 40000

F 1-May-2012 80000

G 1-Oct-2012 15000

table 2

LOAN CREDIT DATE AMOUNT

B 1-Mar-2012 40000

C 1-May-2012 80000

D 1-Oct-2012 15000

B 1-Jul-2012 25000

A 1-May-2012 2000

R 1-Jun-2012 40000

S 1-Jul-2012 80000

i am not an excel expert just have basic ideas.i need your help as i need to calculate the below mentioned question…i dnt know how please help.

q:

One time payment plan(MIS) payment plan(FD)

Months Months

Rank Designation 66 120 36 60 84 120 & above

1 Marketing Member 6% 10% 7% 8% 9% 12%

2 Sr.Marketing Member 1.80% 1.80% 2.00% 2.00% 2.00% 2.00%

3 Sales Executive 1.00% 1.00% 1.30% 1.30% 1.30% 1.30% 1.30%

4 Sr.Sales Executive 0.90% 0.90% 1.20% 1.20% 1.20% 1.20%

5 Sales Manager 0.80% 0.80% 1.00% 1.00% 1.00% 1.00% 1.00%

6 Sr.Sales Manager 0.60% 0.60% 0.80% 0.80% 0.80% 0.80%

7 Sales Inspector 0.60% 0.60% 0.80% 0.80% 0.80% 0.80% 0.80%

8 Sr.Sales Inspector 0.60% 0.60% 0.80% 0.80% 0.80% 0.80%

9 Development Officer 0.60% 0.60% 0.80% 0.80% 0.80% 0.80%

10 Sr.Development Officer 0.60% 0.60% 0.80% 0.80% 0.80% 0.80%

11 Development Manger “0.50%” 0.50% 0.70% 0.70% 0.70% 0.70%

12 Sr.Development Manager 0.50% 0.50% 0.70% 0.70% 0.70% 0.70%

13Regional Marketing Officer 0.50% 0.50% 0.70% 0.70% 0.70% 0.70%

14Regional Marketing Manager 0.50% 0.50% 0.70% 0.70% 0.70% 0.70%

15Chief Marketing Manager 0.50% 0.50% 0.70% 0.70% 0.70% 0.70%

Question is that if the rank is Development Manager and the Product is MIS and Term is 66 months then what would be the income of the person on the basis of the declared percentage(under ” “).Now I need a way that If i put only the rank,term and amount then the earning should come automatically.Please help…

Hi, how can we we get multiple column headers returned in one row.

i.e:

17 18 19 20 21 22

Yes Yes

Yes No

if 19 and 21 row contains Yes, then which formula can return Column Name(i.e 19,21) in 22.

Please help, highly appreciated.

i have 3 column like A, B, C and now i want to formula for A-B=C but in this formula if data is available then by calculation is working fine. but if in some case B Column data is blank then what formula i have to use.

Please help!!!!!

This is a great how and why. I am however getting a #value error because I’m using text, but I’m not sure how to fix that situation as the compare should be returning a number value. essentially I havethe following: index(column A, match(1(column b)*(column c),0)). all columns are text. If I understood correctly I was returning a 1=true,0=false value so I don’t understand the #value error I’m getting. any guidance is appreciated.

I have use this formula to look up value from form but in cell show error #Value!

=INDEX(Timein,)*MATCH(1,(A7=IDList)*(A11=Date),0)

Pleas help me in this problem

Suuuuuuuuper helpful post, Debra, thanks so much!! Very nicely displayed and very clear. Keep up the good work!

Thank you for this tutorial. It has been very helpful.

I do note one problem with it. I’m usually working with data in a variable set of rows or rows that might have blank cells between them. As such, this formula seems to be failing for me.

Using your example, I tried something similar to:

`=INDEX($D:$D,MATCH(1,(A13=$B:$B)*(B13=$C:$C),0))`

but the result was #NUM. I then tried:

`=INDEX($D:$D,MATCH(1,(A13=$B$2:$B$11)*(B13=$C$2:$C$11),0))`

and the return value was 30, the value in the cell just above the desired result. EDIT: I see the mistake in the second code, I forgot to put $D$2:$D$11. It actually does work when I fix that. However, it is curious to me that I may be hitting an Excel array limitation when trying to use full column matching.

A13 and B13 in my example is where I put my values for Sweater and Large.

When I went back to the attempt on my spreadsheet, which was 6201 rows of data, attempted to do the F9 trick on my inner match function, I got a “Formula is too long.” popup error from Excel.

OK, final reply. I figured out what my problem was. There is no array limitation that I was running into. I was confusing a mathematical formula with a string manipulation formula. Still, the $A:$A thing wasn’t working with the example. Now, onto my problem for which there will have to be different solution. (I’ll probably use VBA in a macro.)

When I was first using Match, it was using the first value as my search term. That is, I had a column of data with [nameOfGroup: groupID] and my reference value was only the groupID. So, my Match function looked like (where column A held my indeterminant number of rows of data to be searched and H2 held the groupID being searched):

`match("*"&H2,$A$2:$A$6202,0)`

I modified it, using the example above, to be:

`match(1,("*"&H2=$A$2:$A$6202),0)`

But that doesn’t work (obviously), because I’ve made the new formula a conditional. And the conditional doesn’t work the way I intended it.

Ahh…. now to go stretch my coding brain again.

sorry, final, final post.

My solution, a bit klunky, but it works. Essentially, I use FIND. (SEARCH works, too).

=INDEX($D$2:$D$6202,MATCH(1,FIND(H3,$A$2:$A$6202)/FIND(H3,$A$2:$A$6202))*(I3=$B$2:$B$6202),0))

What is happening is I use the FIND to return the number the value is found within the search term and then divide it by itself, which gives me a 1.

Astute readers will see the problem: “What happens if a match isn’t found?” It will return a divide by 0 error (0/0). Excel handles this situation by putting a #N/A on the line. It shouldn’t matter. You will get the result you want and be able to handle the results not found situations.

I call your page as “EXCEL HEAVEN”

Thanks for the learning! Keep Sharing! :D

Hi, your posts on index match saved me! One strange issue arises, however, with a three-criteria index match. The first two criteria are a date and a ticker symbol. The third criteria is a text string written as “Buy”. When I use the evaluate formula tool, it matches both the date and symbol as a string (ie. “41200Aud.Usd”), then attaches “Buy” at the end, gives the coordinates in the table referenced, then returns a value. In the table, however, no “41200Aud.UsdBuy” exists! The formula seems to be making it up! There is a “41200Aud.UsdCover”, but with the match formula set to 0, this value should be ignored.

Here is the formula:

{=IFERROR(INDEX(‘AC – Financials.xlsm’!AF_I_Ledger[#All],MATCH(1,(‘AC – Financials.xlsm’!AF_I_Ledger[[#All],[Date]]=$B8)*(‘AC – Financials.xlsm’!AF_I_Ledger[[#All],[Ticker]]=$C$3)*(‘AC – Financials.xlsm’!AF_I_Ledger[Transaction]=”Buy”),0),6),””)}

Dates are in column B, symbol cell C3.

The ‘AC – Financials.xlsm’AF_I_Ledger[#All]’ is a data table in another workbook. The formula fails to apply the exact match condition to the third criteria “(‘AC – Financials.xlsm’!AF_I_Ledger[Transaction]=”Buy”)”

If you could tell me where went wrong it would be wonderful!

Thank you!

Ross

Ross

I notice the third criteria does not have the [#all] as part of the reference. Could be the reason it is failing.

TextBox3.Value = (TextBox2.Value * WorksheetFunction.Index(Worksheets(“TABELAS”).Range(“C4:ALO40”), WorksheetFunction.Match(TextBox2.Value, Worksheets(“TABELAS”).Range(“B4:B40”), -1), WorksheetFunction.Match(TextBox1.Value, Worksheets(“TABELAS”).Range(“C3:ALO3”), 0), 1))

or

Label6 = “=(TextBox2*INDEX(TABELAS!R4C3:R40C1003,MATCH(TextBox2,TABELAS!R4C2:R40C2,-1),MATCH(TextBox1,TABELAS!R3C3:R3C1003,0),1))”

please helpme

So not an excel pro! Im trying to understand this for matching purposes… my use for this would be to compare 2 worksheets to find matching items. is there a way for the worksheet to highlight matches found- or something to identify that they match? I work in accounts payable and the way of printing and checking off is so old fashion and time consuming! anything to help me here?

to try to explain it better…

i have our info and our contractors invoice – i need to compare our PO# and price with their invoice (which also has a PO# and price) but i need to make sure i identify it correctly and mark off the match so i can see it….

Help make this quicker for me!!! thanks!

I want to do this except Sweater appears on one tab, and sweater and price appear on another worksheet. On worksheet #2 sweater is not the first field (so VLOOKUP won’t work). Thoughts?

Worksheet #1

Column A-D have stuff with Column E the word “Sweater”

Worksheet #2

Columns A,B,C contain stuff, Column D contains “Sweater”, and column E “Price”

Further complicating matters “Sweater” and “Price” could be in any columns on any one of a dozen worksheets. Any way to search for something across worksheets and return a value from the row where it’s found?

Pls help me wit this

i want a formula to match different criteria(=,=) in different sheet has a reference

Hi

=INDEX(L$42:L$107,MATCH(1,($K$42:$K$107=$J8)*($J$42:$J$107=$I8),0))

how to avoid pressing ctrl+shift+enter

-i am using excel with solidworks , and i use something they call it configure publisher

regards

Ashraf

hI,

=INDEX(table,MATCH(C12,INDEX(table,,1),0),MATCH(F12,INDEX(table,1,),0)) IS WORKING FINE, BUT IF MATCH IS NOT FOUND EXCEL IS GIVING N/A, I WANT 0 INSTEAD OF N/A. COULD ANY BODY COMMENT ON THIS

There is an alternate solution to this, and that’s using an Ampersand within the MATCH formula.

{=INDEX($D$2:$D$10,MATCH(A13&B13,$B$2:$B$10&$C$2:$C$10))}

You still must CTRL+SHIFT+ENTER to make it an array formula, however this entirely eliminates the need to do math and match to a 1. This instead will simply look over both columns at the same time, and both must be true.

This is exactly what I’ve been looking for. Thank you so much!

Empty cell in Excel file has changed in the month, while 0 is 1 reply should have come to help please

Dear excel experts, can you offer any help on which function or combination of functions to use in order to solve the following issue:

I have an excel table (with many columns) that “result” in 4 final columns [columns: Z, AA, AB, AC]

Each cell in each of these columns contains a function. The specific combination of functions used in cell Z2 is: =IF(ISBLANK(M2),”–“,IF(D2=M2,1,IF(D2M2,”X”)))

The result is either — or 1 or X

At the top row of these columns [in cells: Z1, AA1, AB1, AC1] there is a person’s name.

Up to this point all is as it should be.

I now wish, and seek your help for, to be able to do the following:

To input a function (or a combination of functions?) in cell AD2 which will result in the correct person’s name (located in cells Z1, AA1, AB1, AC1) when the value of 1, appears only in one of the 4 cells [cells Z2 through AC2]. Below is the example of my problem:

Columns: Z AA AB AC AD

Row 1 Jane Nick John Dick

Row 2 1 1 1 X

Row 3 1 X 1 X

Row 4 X 1 X X Nick

Row 5 X 1 1 X

Row 6 X X 1 X John

etc.

Can anyone help with suggesting the appropriate function combination for column AD?

I thank you very very much for any tip that anyone can provide (I hope not VBA)!

Apostolos

please help me for stocks location

i have same product in different location, qty

exp: Producta – 100 nos – Loc 12A

Producta – 100 nos – Loc 13A

Iam issuing stocks from loc 12A for 100 and when i check for next issue the location how can i retrive

It is helpful.

but You are returning a value based on and conditions.

but how to return a value based on OR condition .

DB:

Sankar Y

Senthil Y

Sankar Y

Vinod N

Gokul Y

Senthil N

Should return a Values which name is either (Senthil and Y)–If anyone Condition Satisfies means it has to return

Hello,

I have trouble using the INDEX & MATCH with multiple criteria.

I have multiple Excel files containing a lot of data that looks like this:

MSC/NASTRAN

END LOADS

ELEM GID1 GID2 TYP 3000000 3002100 3002104 3002111 3002205 3002219

0 1070017 9185117 P 74.1 2.2 82.8 74.2 21.3 57.8

0 1070017 9185118 P 98.4 82.4 10.8 0.3 21.4 72.5

0 1070018 9185114 P 74.6 43.1 18 86.4 19 32.2

0 1070018 9185115 P 22.5 41.6 74 98.1 58.9 28.9

0 1070019 9185113 P 87.5 14.9 63.6 92.7 26.2 37.9

0 1070020 9185112 P 34 11.1 83 51.6 66.5 59.1

0 1070067 9185116 P 99.9 76 61.1 23.5 2.8 79.7

0 1070517 9185617 P 35.3 82.4 63.4 7.5 36.8 55.5

0 1070517 9185618 P 11.6 70.7 89.7 17.3 95.4 29.5

0 1070518 9185614 P 16.7 66 87.1 31.4 10 48.5

0 1070518 9185615 P 49.5 72.9 62.5 68.3 93.6 51

0 1070519 9185613 P 18.4 0.9 51.6 20.3 72.2 6.5

Please note that this file contains thousands of rows and hundreds of columns…

In a separate file that I call EXTRACTOR, I want to extract data from the previous file.

There are 4 criteria that I want to check: ELEM, GID1, GID2 AND TYP

In the EXTRACTOR, the user has to specify the name of the file from where he wants to extract data. He is also asked to give then name of the sheet (please note that the file containing the data has to be open)

Name of file containing all loads (with correct extension): loads.xlsx

Name of sheet containing all loads: sheet1

Then I do a CONCATENATE of these 2 entries in cell N6 (in EXTRACTOR) in order to have: [loads.xlsx]sheet1!

This will be used further in cell E12 formula…

Now, in EXTRACTOR, I type (starting at cell A11):

ELEM GID1 GID2 TYP 3000000 3002100 3002104 3002111 3002205 3002219

0 1070017 9185117 P #N/A

Cell E12 displays “#N/A” and contains the following big formula:

= INDEX(

CONCATENATE($N$6, ADDRESS(6, COLUMN(E10),2),”:”, ADDRESS(15000, COLUMN(E10),2) ),

MATCH(1,

($A12= CONCATENATE($N$6,”$A$6:$A$15000″) )

*

($B12= CONCATENATE($N$6,”$B$6:$B$15000″) )

*

($C12= CONCATENATE($N$6,”$C$6:$C$15000″) )

*

($D12= CONCATENATE($N$6,”$D$6:$D$15000″) ),0),1

)

This formula doesn’t seem to work properly…

When I do EVALUATE FORMULA, I get FALSE in every MATCH criteria check, which in the end gives me #N/A within the MATCH, and also #N/A for the global formula

I hope my description of the problem is clear enough.

If somebody can help, it would be very very appreciated!

By the way, using MS Excel 2010 on XP 64 bits

Thank you!

J.

Hi, This is a very helpful tutorial. I was wondering that, how I can use Match function to find a value in a range of cells between certain values such as,

=INDEX($A$4:$A$12,MATCH(1,(G13>=$E$4:$E$12)*(G13<=$F$4:$F$12),0)). Thanks in advance

I’ve used the index/match array version to lookup multi-critreria data and it works great! However, I’m experiencing a problem when sorting on columns outside the column that contains the array formula. It seems as if the relative cell references do not follow the sort. Can anyone help or explain why this may happen?

Thanks!

@Josh: Can you post a sample workbook somewhere, or explain a little bit more fully what your data looks like and what you are trying to do?

@Joaquim: This is a little hard to conceptualize. Can you post a sample workbook somewhere and post the link here?

@Jeff: Thank you for your reply! I will provide such a sample at the beginning of next week. Thanks again :-)

P.S.: Any suggestion where to put the sample?

@Sankar: Your question doesn’t make sense to me…can you clarify further? You say you want to use an OR condition, but your example ” return a Values which name is either (Senthil and Y)” is a single AND condition wrapped in an OR condition.

@Sobuj: That formula works fine, PROVIDED you array enter it. i.e. push CTRL + SHIFT + ENTER to enter the formula. It will then appear in the formula bar with curly brackets surrounding it:

{=INDEX($A$4:$A$12,MATCH(1,(G13>=$E$4:$E$12)*(G13<=$F$4:$F$12),0))}

@Joaquim: Save it somewhere like Google Docs or SkyDrive or DropBox; make the file public, and post a link here.

@Jeff: What I’m trying to do is work related and since I can’t use Google Docs, SkyDrive or DropBox at work, I think it would be easier to send you the file directly to your inbox. Is that ok with you?

@Jeff: What I’m trying to do is work related and since I can’t use Google Docs, SkyDrive or DropBox at work, I think it would be easier to send you the file directly to your inbox. Is that ok with you?

@Joaquim…sure, although I’m on holiday over the next week and so might not get a chance to look at it for a while. weir dot jeff at gmail dot com.

So interesting hybrid!

I tried to use that multi-criteria ‘Match+Index’ command in a macro while refering to arrays built in the same macro, but I received “Type mismatch” error!

How can I use it in a VBA routines not using any worksheet.range/cell address?

Hi Debra

I’d like to use the match function (or any other you suggest better) to filter a table under one criteria and display the filtered records in a drop-down list.

For example, I have a list of electrical devices that operate at different voltages and I want to display in a drop-down list only those devices that operate at 277 volts.

How to configure the dropdown to achieve this task?

Thanks much!

AG

AG: This might help – http://www.contextures.com/xlDataVal15.html

Or this one – http://www.contextures.com/xlDataVal02.html

Personally I prefer the first approach, although you might find that the 2nd is simpler.

Hello

Ive successfuly used Index and Match but I have a problem

For example the data is

green apple | wallmart | 5

green apple | wallmart | 23

red apple | wallmart | 5

orange | macy’s | 4

I want to get all green apple’s sold in wallmart, and get the amount of each for example

5,23,5

when using index match, i can only get the first one it matches to.. how to get multiple values and concatenate them like this?

Thanks! I’ve been trying to figure this out all day! It works on my worksheet! WooHoo!!

Liked A. Roberts ampersand solution and having done it with the earlier one, I cannot get the ampersand to work. This is even when copying and pasting his formula?

I am trying to use and Index & Match function where the matches are coming from a Data Validation List and the indexing criteria & matches are in another tab. It appears to me I’ve written the formula ok, but am still getting the #N/A result, which I’m guessing is because of the data validation list. Any thoughts? Below is the formula I’ve written.

{=INDEX(‘All Data’!$K:$K,MATCH($C$19&$E$19&$H$19,’All Data’!$A:$A&’All Data’!$C:$C&’All Data’!$E:$E,0),1)}

E19 & H19 are the match criteria from the data validation list. All three criteria need to be met in order to return the correct value in All Data column K.

I appreciate any help you can give me.

Hi. I am trying to compare two array and find duplicates, I already check for index, match do not work, and(exact do not work, conditional formatting countif do not work, let me give you a little example.

13 16 17 40 42 44 10 11 12 17 28 46

1 12 22 44 46 52 3 9 11 21 24 49

10 13 35 36 46 42 11 24 36 45 46 47

9 26 34 40 42 49 3 6 36 46 48 52

1 2 25 43 48 53 2 7 11 15 43 45

8 32 35 46 47 52 3 6 16 30 31 40

20 44 46 48 52 53 20 44 46 48 52 53

15 17 22 40 41 45 29 33 46 48 49 53

@victor you can use SUM and COUNTIF in an array-entered formula.

For example, if the first sets of numbers are in B2:G2, and I2:N2, put this formula in cell P2:

=SUM(COUNTIF(B2:G2,I2:N2))

Then, instead of pressing Enter, press

Ctrl + Shift + Enterto array-enter the formula.Copy the formula down to row 7.

sorry is two arrays 6by6 the first six are 13.16.17.40.42.44 compare against 10.11.12.17.28.46 and so on thanks.

Hi Debra, I tried but do not work.

@Vicktor what happened when you tried it?

What separator do you use in other formulas? Perhaps you need to use a semi-colon, instead of a comma:

=SUM(COUNTIF(B2:G2;I2:N2))

Thanks for your time, I write exactly what you say, but pop a message formula have error.

Did you press ctrl + shift + enter

Which separator did you use — comma or semi-colon?

the formula you type have an error, pop the same messages. thanks. anyway I got a vba code that resolve my problem.

again thank you so much for reading my post and tried to help. keep in touch.

Hi,

SIR I HAVE A SHEET IN WHICH I HAVE 6 COLUMNS FOR RICE INVENTORY.

1)Report No

2)IN/OUT/PROCESS/Fg(This column show whether Rice In, out,Proceeds & Finshed goods)

3)Product Name

4) In (If In & FG i have make entry of quantity in this column)

5) Out (If out or process i have make entry of quanitity in this column)

6) Balance

Now i want that balance column check the report number, product name , & whether it is IN OUT Or other so it give me balance

If it is IN or FG so it add or if it out or Process its less it.

Hai,

Please tell me how I can retrieve data from below two worksheets

sheet 1:

Date Sales

15/10/2013 Honda

20/10/2013 Benz

23/10/2013 Toyota

Sheet 2:

Date Sales

if I enter the date on the second sheet, how I can retrieve sales data using formula

Hoping to hearing from you soon

Thanks

=IF(C12=”STD”,INDEX(EQ!$B$10:$K$11,MATCH(EQ!$B$52,EQ!$B$10:$B$11,0),MATCH(EQ!B13,EQ!$B$10:$K$10),IF(C12=”LED”,INDEX(‘EQ1’!$B$10:$K$11,MATCH(‘EQ1′!$B$52,’EQ1’!$B$10:$B$11,0),MATCH(‘EQ1′!B13,’EQ1’!$B$10:$K$10)))))

Hi, i hope you can help me.

i want to lookup data in 2 defferent =EQ= and =EQ1= worksheets by changing the data =MainPage= worksheet by changing data in cell c12 on =MainPage+

Hi,

First of all, thanks for this article.

I have data like this..

Region State Segment Sales

Central Illinois Corporate 5.9

West Washington Corporate 13.01

West California Home Office 6362.85

East Massachusetts Home Office 232.95

Central Minnesota Small Business 1164.45

East New Hampshire Home Office 705.47

West Washington Consumer 299.74

like this, i have data more than 9000 rows for these 4 regions in random order.

I would like to see the data separately for regions. i.e all the rows of Central region should come as one data set and all the rows of East region should come as another data set and so on..

I did this using VLOOKUP function.

But i need the solution using INDEX and MATCH functions.

Please suggest me..

I don’t think I fully understand what you mean but it sounds like a problem you could solve using a pivot table

All of these tips are so very helpful for potenetial issues. I have not been able o identify how to pull my challenge into any of these formulas. Can anyone assist?

Columns/Row: C7 through H7 Display “Year 1” “Year 2” etc. through “Year 6”

Columns/Row: C30 through H7 Display a cumulaative total based on formulas in preceeding rows.

The goal is to return the year (in cell H33) when the “Positive Payback” has been achieved.

Example: C30 may be -365 / D30 may be -290 / E30 may be -188 / F30 may be 25 / G30 may be 150 / H30 may be 250

The end result should display “Year 4” Since this is the year it bacame a positive number.

Any ideas?

I have a trial balance from work which has account numbers vertically and company codes horizontally, along with account balances (data tab). I am trying to write a formula on another tab in the same workbook that will display the account balance based upon a specific company code and account number specified on another tab in the workbook. I have tried the index/match formula but cannot seem to get it to work. I am hoping to be able to replicate the formula, as well as drop new data (same format) into the data tab each month.

Any ideas?

=IF(C12=”STD”,INDEX(EQ!$B$10:$K$11,MATCH(EQ!$B$52,EQ!$B$10:$B$11,0),MATCH(EQ!B13,EQ!$B$10:$K$10),IF(C12=”LED”,INDEX(‘EQ1’!$B$10:$K$11,MATCH(‘EQ1′!$B$52,’EQ1’!$B$10:$B$11,0),MATCH(‘EQ1′!B13,’EQ1’!$B$10:$K$10)))))

Hi, i hope you can help me.

i want to lookup data in 2 defferent =EQ= and =EQ1= worksheets by changing the data =MainPage= worksheet by changing data in cell c12 on =MainPage+

Sended on the 12 July but no response yet, can somebody please help

You did a few errors:

1) referring to a cell using ‘EQ1’!$B$10 instead of EQ1!$B$10

2) MATCH() has 3 arguments. If you don’t specify the third argument, MATCH will find the largest value that is less than or equal to lookup_value

3) you forgot some brackets. You need to put INDEX inside brackets.

4) you might want to specify the value_if_false argument in your second if. I would put something like “error” or “not found”.

You want =IF(C12=”STD”, INDEX() , IF(C12=”LED”, INDEX() , “not found” ) )

I was hoping someone could help me with my problem as follows;

Column A B C D E F G H I J

Concentrates Blend Mineral Concentrate Soya 21 10 11 11 10

And so on, I have more columns than this and lots of rows of date. What I would like to do in column K is total what is concentrates, ie 32, in column L total blend which is 10 and so on.

Please could someone advise a formula I can use. Thank you

Hi,

By using Index and Match formula how we can get Multiple result.

Example:

Row Labels Sum of Catches7

Dnyaneshwar Vaidya 2

Rohan Handibag 1

Farhan Nehari 2

Sandip Ghule 1

In given example Two Person Names Having same Value.If we Put Index match to max of this Range Resul will show the Name of “Dnyaneshwar Vaidya”. But same value is against another Person also.So How to get Both the Names or all the Name of Same Value if available.

Please help me in this.

Hi All,

I have data in sheet one with employee Id number, month and Hours Billed Data ranging from column C to P Now I want the data on sheet 2 where I want the Hours billed data for specific id(which repeats multiple times in column C2:C95) for specific month in column L2:L95 the result the match for C and L Hours billed in sheet2? Anyone can help

How to select value for A-152-4 as 33 and B-152-4 as 304 using multiple index & match formula . Pl help me

1 2 3 4 5 6 7 8 9 10 11

A

147 201 202 203 204 205 206 207 208 209 210 211

152 30 31 32 33 34 35 36 37 38 39 40

136 50 51 52 53 54 55 56 57 58 59 60

852 70 71 72 73 74 75 76 77 78 79 80

963 90 91 92 93 94 95 96 97 98 99 100

B

147 101 102 103 104 105 106 107 108 109 110 111

152 301 302 303 304 305 306 307 308 309 310 311

136 501 502 503 504 505 506 507 508 509 510 511

852 701 702 703 704 705 706 707 708 709 710 711

963 901 902 903 904 905 906 907 908 909 910 911

sir we have one problem plz solve this

,=INDEX(performance!$E$2:$L$301,MATCH(today!$E2&today!$F2&today!$G2&today!$H2&today!$I2&today!$J2&today!$K2,performance!$E$2:$E$301&performance!$F$2:$F$301&performance!$G$2:$G$301&performance!$H$2:$H$301&performance!$I$2:$I$301&performance!$J$2:$J$301&performance!$K$2:$K$301,0),8)

this formula was given exject but we want +/- 3% range between plz solve my problem

thank u Advance

Hello,

I have tried to use the match index formula based on the above comments, bur getting error message NA.

=INDEX($O$5:$O$76,MATCH(1,(E8=$L$5:$L$76)*(E9=$M$5:$M$76),0))

Can someone let me know if am making any mistake in the above formula.

Shalini,

First of all, remember to use Ctrl + Shift + Enter when entering the formula.

That said, I believe #N/A message appears because there is no match for the criteria you select.

Make sure the combination you are trying to match really exists (E8 and E9).

I have the same problem, i need the #N/A to show as 0 instead. Please could someone help with this.

Thanks

AWESOME…..

I WAS STRUGGLING TO FIND SOLUTION TO EXTRACT DATA ON MULTIPLE CONDITIONS WITHOUT USING CONCATENATE FUNCTION… BUT WAS NT ABLE TO GET IT.

cONTEXTURES BLOG GAVE ME SOLUTION

THANKS A LOTTTTT……..

Hi!!! I’m trying to match all the available codes from an old coding version to a new one. For example, I got a Table with all the possible combination between codes. Also have another table that got service codes and its old codes, I need to match it with the table that got the possible combinations.

Example:

***CodeTable***

OldCode NewCode

013.2 A17.81

192.1 C70.0

192.1 C70.9

228.02 D18.02

346.7 G43.701

346.7 G43.711

346.7 G43.709

346.7 G43.719

784.51 R47.01

V12.54 Z86.73

***Service Codes***

ServiceCode OldCode NewCode

70450 013.2 ??????

70450 192.17 ??????

70450 228.02 ??????

70450 346.7 ??????

70450 784.51 ??????

70450 V12.54 ??????

I’ll appreciate your help…

My application has some blank spaces. Imagine in the example above if the first three items did not have code numbers. In other words, A2, A3 & A4 were blank. I am trying to achieve the rational that “code” only matters when there is a code, else ignore it. My formula is: =INDEX(Fb,MATCH(1,INDEX((A4=SYSTEM)*(B4=SPECIE)*(C4=SIZE)*(D4=GRADE),0),0))

For some systems, size isn’t pertinent and those cells are left blank. My formula works in all other combinations of system,specie,size, and grade but not when “size” is blank. I need to turn off the string *(C4=SIZE) when the cells in the “size” range are blank. In essence, I need to reduce the formula to: =INDEX(Fb,MATCH(1,INDEX((A4=SYSTEM)*(B4=SPECIE)*(D4=GRADE),0),0)) for certain values of “system”. Any ideas on how to do that?

Yeah, use an IF statement:

=IF(ISBLANK(SIZE), INDEX(Fb,MATCH(1,INDEX((A4=SYSTEM)*(B4=SPECIE)*(C4=SIZE)*(D4=GRADE),0),0)) , INDEX(Fb,MATCH(1,INDEX((A4=SYSTEM)*(B4=SPECIE)*(D4=GRADE),0),0)) )

Joaquim,

Thanks. I used =IF(OR(A4=”MSR”,A4=”MEL”),INDEX(Fb,MATCH(1,INDEX((A4=SYSTEM)*(B4=SPECIE)*(D4=GRADE),0),0)),INDEX(Fb,MATCH(1,INDEX((A4=SYSTEM)*(B4=SPECIE)*(C4=SIZE)*(D4=GRADE),0),0)))

We are thinking alike, but my new problem is that it only works as long as it is in the same worksheet as the ranges. I need index/match to work across workbooks. My data workbook “DESIGNVALUES” contains all of the design values. Each project workbook needs to retrieve design values from “DESIGNVALUES” but I haven’t been able to get index/match to work unless it is in the same worksheet with the data ranges. I have searched several blogs and it is a common topic but so far I’ve not seen a solution. People who say that it should work in 2010 haven’t tried it. I don’t know if it will work in 2013.

Dear all,

Good evening,

Anyone can help me to my problem, I have two working worksheet in excel

1. as my data entry, which you can find in every column the date, passport number, name, training course, result(Passed,Failed,No Show). I have more than a thousand person name with corresponding passport number and every person have more than twenty training need to attend. Mean in one person need to view a more line if your find it with a different date taken and result.

2. second worksheet have passport number and all training course title in horizontal line.

in the second worksheet under the training title the argument(condition) should be, if passport number(second worksheet) match to passport number(first worksheet), training course title(second worksheet match to training course column of the first worksheet, then check in the column of result(first worksheet) if found passed must to appear the date of was taken in the column of training course title in the second worksheet. what is the best formula for excel to get this. thanks

Genius! Thank you! I am using this to calculate a timesheet budget based on hourly rates, day rates, etc. It works perfectly on all except hourly as it keeps coming up 10x the correct amount. Perhaps Excel is telling me I need a raise?

@Robin, a raise is a great idea! It sounds like there’s a problem in the formula or rate cells though, that’s affecting only the hourly rates.

Hello- I am attempting to do something similar…. I am currently using this formula =VLOOKUP(LARGE(A:A,1),A1:M63,12,FALSE). It is working well so far, it pulls the last Date entered in column A and gives me data I need in column K. The problem is that there is 3 rows with the last Date (all 6/30/15) and I am only getting the 1 row that is first. Any ideas? Thanks

In the many examples above, we need to have an exact match. Is there a way I can mark the result as “0” if there is no exact match? I’m trying to subtract the finding if there’s an exact match but my results give me #N/A.

=INDEX(‘sample’!J:J,MATCH(1,INDEX((‘sample2’!$H:$H=$H59)*(‘sample’!$E:$E=$E$7),,),0),1)

Thank you in advance.

Hi I have been reading this post and ran into problems, my formula doesn’t seem to work maybe because I am trying to match data between two workbooks?

So far I have tried the way in the main example, dividing the FIND formula, and the “&” added to the formula…all with no success.

These have been my attempts:

=INDEX(‘[Book1.xlsx]Sheet1′!$J2:$J5294,MATCH(A2&B2,'[Book1.xlsx]Sheet1′!$W2:$W5294&'[Book1.xlsx]Sheet1’!$AE2:$AE5294))

=INDEX(‘[Book1.xlsx]Sheet1′!$J:$J,MATCH(1,(A2=’C:\Users\Desktop\[Book1.xlsx]Sheet1′!$W:$W)*(B2=’C:\Users\Desktop\[Book1.xlsx]Sheet1’!$AE:$AE),0))

In both cases I press Shift+Crtl+Enter and I am looking for a way in which I do not need to specify the range, as the data from “Book1.xlsx” is not always 5294…

I have been stuck on this for a long time…please help :(

…if there is an easier way in vba I would like to know as well! :)

HI thanks for Sharing excellent tricks.

I also need your help in summing data through selecting multiple option dropdown.

Ex.

My data is in column like April, May,June,…..March.

I created two dropdowns for Months through data validation,

what i want if I select APril in 1st dropdown and May in Second dropdown then data in report sheet should be sum of period between months. same way if I select April in 1st dropdown and june in 2nd dropdown, it should fetch the sum of april+may+june.

please help me if any body have any idea.

Thank in advance

I have attempted to use these formula examples but I can’t get it to work with the files I am using.

I have two separate files that I need to create formula for, similar to a VLOOKUP formula, but that will search for multiple criteria and insert the result when finding an exact match.

The way my 1st excel file appears is:

Parent UPC (Carton) Child UPC (Pack) Item Description

Camel Crush Menthol

The second file contains the data I need to extract from. I would like to write a formula that searches my database file for the description in C2 (Camel Crush Menthol) and, in this second file, an Item size with Carton, and once it finds the results, inserts the the results into A2 (and also B2 when searching for an item size “Pack”)

I am working with a large amount of data in one worksheet and need to pull data from one field that is within a row that matches three text based criteria into a second worksheet.

I have tried many combinations, but this is my most recent attempt.

=INDEX(‘Zone 11’!$H:$H,MATCH($k11,IF(DataDump!$E:$E=$A11)*(DataDump!$G:$G=”Q1-2015″)*(DataDump!$N:$N=”2015″),0))

Can you help me sort this out?

Hi All ,

i have 3 column; findin lil difficult , can any1 help me 1st match is row ref and 2 nd match is column ref =INDEX(B3:$AJ$50,MATCH($B$1,$F$3:$F$50,0),MATCH($A$1,$F$2:$AJ$2,0))

Is there a way of checking 2 columns to see if they are both a match, and then if they do match, check along that row and look up all of the columns that have an “X” in them and reurn all of the column numbers into a single cell?

EG.

The table I want to look up the culum numbers from looks like:

EXP | TEST | – | 1 | 2 | 3 | 4 | 5 |…| 25 |

2 | 7 | – | X | X | X | X | X |…| X |

11 | 13 | – | | | X | | | | |

13 | 4 | – | | | | | X | | |

13 | 9 | – | X | | X | | | | |

. | . | – | | | | | | | |

. | . | – | | | | | | | |

. | . | – | | | | | | | |

Then in a seperate worksheet, I have the EXP & Test numbers like so:

EXP | TEST | Column No |

52 | 4 | – |

84 | 7 | – |

84 | 12 | – |

. | . | – |

. | . | – |

. | . | – |

. | . | – |

I am wanting to use the values from this 2nd table to look up matching cells in the 1st table (ie when both the EXP AND Test number match) and then look across that column and whenever there is an “X”, it gives back the number of the column and puts it in a single cell in the “Column No.” column.

EG.

EXP | TEST | Column No |

13 | 9 | 1, 3 |

52 | 4 | … |

84 | 7 | … |

Thanks,

Bob

I’m looking for some solution where INDEX MAtch find the output of my keyword based on a condition.

I’m having list of city names with change values in front for each city. In my second sheet i’m calling the value to give me value for City XYZ if the district is ABC.

Currently I’m using ” =INDEX(Sheet1!$D$3:$D$13,MATCH(C3,Sheet1!$C$3:$C$13))”

But the problem is that it pick first come city value.

For Exacmple: In my BASE sheet I’ve Jordan three times under different Districts and each times its value is changed. When i call it from my data entry sheet it picks up the first value and do it again whenever the word “Jordan” comes whereas the value is change for another “JORDAN”

Thanks in Advance,

I got the solution from Sample file Example 4 by customizing :)

@Naveed, great! Thanks for letting me know that you were able to get it working.

This formula works great! Thanks so much for posting.

I am not excel savvy, however, was able to follow the instructions and get the formula to work.

Is there a way to combine exact match(case sensitive) to the 2 criteria?

I have a formula {=INDEX(Sheet2!$A1:$H5,MATCH(B2,Sheet2!$A1:$A49),MATCH(D4&A5,Sheet2!$A1:$Z1&Sheet2!$A2:$Z2,0))}. I am looking up for three conditions–if there is a specific string in row 1 and another specific string in row 2 as well as a specific string (a first name) in column A.

I have 3 mock first names and I enter one into a particular cell, and the Index function does it’s thing. I can then enter a different name into that cell and different values will be pulled via INDEX, and so forth. The issue is this formula works for 2 of them, but not the third. They are all formatted as strings (or numbers), and the formula is entered as an array. I am completely baffled. HELP!!!

how can i find the date that matches 2 different criteras?

in the criteria is cell F in the shirts database is numbers 1 to 50

in the criteria is cell G in the shirts database is A name

in cell A29 of shirts must match that of shirts database cell b2:b51 and show the date that is next to his name matching the number in shirts a29.

this is what i got but it dont work

=IFERROR(INDEX(‘Shirt Database’!$A$2:$A$51,SMALL(INDEX((Shirts!$F$2:$F$51=$A$29)*ROW(‘Shirt Database’!$A$2:$A$51),0)

thank you for your help!

A B

B001 E127P22

B001 E127N83

B001 E127P41

B001 E127P34

If every-time column A is repeating, i want column B to change every-time. can anyone suggest the formulae???

Hello, I started using the INDEX MATCH and it works good, but everytime i close and open the file, it reads everything as a formula again. I have to do ctrl+shift+enter again… Do I have to do these everytime I close and open the file?

@Stephen

For Excel 2007, go to file > options > formulas. Under category calculation options select there is a manual option you can select. Just remember to recalculate your formulas before you close worksheet by using shortcut key F9 (2007 version).

@Debra Thank you for posting this example! I had to edit my formula after reading your explanation “To find the product code for the selected item and size, you would change the formula to look in cells A2:A10, instead of the price column.” I tried extract data from a =INDEX($A$2:$A$10, which I had named so I had both =INDEX($A$2:$A$10, and price column. I deleted the price column and it saved me time.

My final formula: {=INDEX(‘Medicare Advantage Plans’!$AI$6:$AI$10708,MATCH(1,(‘Medicare Advantage Plans’!$AE$6:$AE$10708=G1896)*(‘Medicare Advantage Plans’!$AF$6:$AF$10708=H1896),0))}

@Diana Thanks for sharing your revised formula!

Very Helpful:-)

=INDEX(H:H,MATCH(MAX((R$2=G$2:G$25000)*(BH$1=F:F),0),0))

this works but brings back the first instance encountered, I need to brig back the Max or min instance encountered.

help?

Did you press Ctrl+Shift+Enter after typing the formula?

I’m running into an issue with this function whereby I have 2 drop down lists of variables to match; one list contains 3 items, the other 2. The function works great for 2 of the list of 3 and 1 of the list of 2. I’ve gone through the formulas 3 times, I’ve checked for spelling errors. I don’t get an #N/A error, no #VALUE error, just nothing comes through when I select one of the variables. When I highlight the formula as described above and look for the 0,0,0,1,0,0…there is no “1” for the list item that is not working. What could possibly be the problem?

I am trying to do the same thing in my workbook as you have done here. I have tried several different formulas and get inconsistent results. I used your formula and changed the ranges to match mine

=INDEX(Formulas!$F$2:$F$100,)MATCH(1,($D12=Formulas!$D$2:$D$100)*(E12=$E$2:$E$100),0))

and I get an N/A error. I have entered with Ctrl+Shift+Enter. Though I want to take it one step further. I want to take the formula one step further and add a quantity multiplier to the formula. =(INDEX(Formulas!$F$2:$F$100,)MATCH(1,($D12=Formulas!$D$2:$D$100)*(E12=$E$2:$E$100),0)*B12) In several versions of the formula it works but in some multiplying by a quantity changes the row that it returns the results from. Any thoughts as to why it isn’t working in my workbook?

Hi David

I had the same problem using this formula – getting the N/A error. I’ve gone back and forth via Google, and finally got my problem sorted. Try using Ctrl+Shift+Enter when you enter the formula into Excel. This then recognises the formula as an array (putting {} around the formula – do not do this manually, it doesn’t work!). Hope that sorts your problem out?

Thanks! Very helpful.