NDEX/MATCH, Multiple criteria

[AnenD]

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

 

[gaz_chops]

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]

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]

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

 

[AnenD]

ADVERTISEMENT

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]

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.

[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]

ADVERTISEMENT

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:

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

[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]

[/QUOTE]

 

[AhoyNC]

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]

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]

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.