# NDEX/MATCH, Multiple criteria

#### AnenD

##### New Member
I have data in columns A, B, and C in sheet1 and data in columns E, F, G, and H in sheet2. I have written an index/match formula in column D in sheet1 to match the data in columns A, B, and C with data in columns E, F, and G in sheet2 and return the data from column H in sheet2. I have checked to be sure that the formula is correct (=INDEX(Sheet2,MATCH(A2&B2&C2,E2&F2,G2,0),8); but the problem is that when a match is found, it returns the data from the row below the match.

Any ideas would be appreciated!!

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### gaz_chops

##### Well-known Member
Try something like

=INDEX(Sheet2!H2:H100,MATCH(A2&B2&C2,Sheet2!E2:E200&Sheet2!F2:F200&Sheet2!G2:G200,0))

Enter as an array formula (Ctrl, Shift & Enter)

#### AnenD

##### New Member
That did it!! THANK YOU!! Why did it work this way but not when I used named ranges?

Try something like

=INDEX(Sheet2!H2:H100,MATCH(A2&B2&C2,Sheet2!E2:E200&Sheet2!F2:F200&Sheet2!G2:G200,0))

Enter as an array formula (Ctrl, Shift & Enter)

#### gaz_chops

##### Well-known Member
You're welcome, i didn't see any named ranges! And you have to enter as an array.

#### AnenD

##### New Member
Ahhh! You are right. I used named ranges in the worksheet, tho, and they didn't work. I also entered the formula as an array. Still I got the data from one row down for some reason. Thank you again!

You're welcome, i didn't see any named ranges! And you have to enter as an array.

#### AhoyNC

##### Well-known Member
Take a look at the actual range your named range for INDEX refers to. If it starts in row 3 instead of row 2 as in gaz-chops example. That could explain the issue.

Code:
``[COLOR=#333333]=INDEX([/COLOR][COLOR=#ff0000]your named range[/COLOR][COLOR=#333333],MATCH(A2&B2&C2,Sheet2!E2:E200&Sheet2!F2:F200&Sheet2!G2:G200,0))[/COLOR]``

#### AnenD

##### New Member
Yes, I checked the named ranges and they start in row 2 so those are correct. The values returned come from the correct column; they are just one row off. This is the code with the named ranges that I used:

Code:
``````=INDEX(named_range, MATCH(A2&B2&C2, [I]named_range_for_column_E&named_range_for_column_F&[I]named_range_for_column[/I]_G,0), column #)

[/I]If you see anything that would explain the problem, that would be great!

[QUOTE="AhoyNC, post: 4182659, member: 195341"]Take a look at the actual range your named range for INDEX refers to. If it starts in row 3 instead of row 2 as in gaz-chops example. That could explain the issue.

[/QUOTE]

#### AhoyNC

##### Well-known Member
Try running just the MATCH part of your formula to see if it returns the right row or if it's one row off. This may help narrow down where the problem is.

=MATCH(A2&B2&C2, named_range_for_column_E&named_range_for_column_F&named_range_for_column_G,0)

#### AnenD

##### New Member
Match does return the right row. Now what?

Try running just the MATCH part of your formula to see if it returns the right row or if it's one row off. This may help narrow down where the problem is.

=MATCH(A2&B2&C2, named_range_for_column_E&named_range_for_column_F&named_range_for_column_G,0)

#### AhoyNC

##### Well-known Member
Don't know. If MATCH returns the right row number and if you count down that many rows in named_range and it gives the right answer the formula should work.

Replies
1
Views
205
Replies
0
Views
540
Replies
5
Views
78
Replies
6
Views
286
Replies
10
Views
148

1,195,669
Messages
6,011,064
Members
441,580
Latest member
BornholmerBjarne

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back