look up multiple values both in rows and columns

**xcl** · 12-29-2011, 06:28 AM

Hi,

I have a grid arranged across rows and columns with total 54 values. I want to extract all the values from the grid and display them in a column against appropriate parameters. I need to use look up feature and have tried index & match but it just hasn't worked. I have attached a file with sample data; it will be great if you can show me a formula that I can use to resolve it.

Thanks,

Nil

**DonkeyOte** · 12-29-2011, 06:35 AM

If as implied you're using XL2007 and your result is always numeric I would suggest you opt for SUMIFS:

Please Login or Register  to view this content.

**Andy Pope** · 12-29-2011, 06:41 AM

You can use a SUMPRODUCT formula in O3 and then copy down

=SUMPRODUCT(($A$3:$A$11=K3)*($B$3:$B$11=L3)*($C$2:$H$2=M3)*($C$1:$H$1=N3)*($C$3:$H$11))

**xcl** · 12-29-2011, 06:50 AM

Ote, thank you so much. I am new to Excel 2007 and to sumifs, it didn't work first time but I guess I would be able to figure out my error.

Andy, thanks for your reply, it was perfect. I did try using sumproduct but was made a mistake.

Thank you guys!