Index/match

**adaws** · 01-04-2012, 06:23 AM

Hi,

Need help with the attached sheet, can anyone give an INDEX and MATCH function to get the results in sheet 2 from the data in sheet 1. Please help.

Thanks

**Bob Phillips** · 01-04-2012, 06:31 AM

Try this array formula (i.e. commit with Ctrl-Shift-Enter)

=INDEX(Sheet1!$E$3:$J$13,MATCH(1,(Sheet1!$C$3:$C$13=C$3)*(Sheet1!$B$3:$B$13=$B4),0),MATCH($B$3,Sheet 1!$E$2:$Z$2,0))

**Fotis1991** · 01-04-2012, 06:36 AM

Hi

In Sheet2!C4, try this formula. Copy down and down.

=INDEX(Sheet1!$E$3:$J$13;MATCH(Sheet2!$C$3;Sheet1!$C$3:$C$13;0);MATCH(Sheet2!$B$3;Sheet1!$E$2:$J$2;0))

Hope to helps you.

**adaws** · 01-04-2012, 06:36 AM

i tried using it, but its giving me a NA error.. is there anything that i am doing wrong, i did commit with Ctrl+Shft+Entr

**adaws** · 01-04-2012, 06:39 AM

there are two criterias that need to be matched in row function of the Index Function , but i guess your formula is looking up just one

**Fotis1991** · 01-04-2012, 07:06 AM

Pls take a look to the attachement..

**adaws** · 01-04-2012, 07:16 AM

The values in sheet 2 B4:B6 is not being looked up any where in the formula

**Haseeb A** · 01-04-2012, 07:56 PM

Looks like you are on XL2007 or later. If so try SUMIFS,

Sheet2!C4, copy down & across.

=SUMIFS(INDEX(Sheet1!$E:$J,0,MATCH($B$3,Sheet1!$E$2:$J$2,0)),Sheet1!$B:$B,$B4,Sheet1!$C:$C,C$3)