look up value based on multiple criteria across row and columns

[imran1059]

Hi,

I have this table to look up. WR and CR are in first row and END and TND are in first column. In this report, there are total of 10 columns and 7 rows.

[TABLE="width: 814"]
<colgroup><col><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]WR[/TD]
[TD]WR[/TD]
[TD]WR[/TD]
[TD]WR[/TD]
[TD]CR[/TD]
[TD]CR[/TD]
[TD]CR[/TD]
[TD]CR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]SOI[/TD]
[TD]SOI[/TD]
[TD]SC[/TD]
[TD]SC[/TD]
[TD]SOI[/TD]
[TD]SOI[/TD]
[TD]SC[/TD]
[TD]SC[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]%Kw[/TD]
[TD]%Rg[/TD]
[TD]%Kw[/TD]
[TD]%Rg[/TD]
[TD]%Kw[/TD]
[TD]%Rg[/TD]
[TD]%Kw[/TD]
[TD]%Rg[/TD]
[/TR]
[TR]
[TD]END[/TD]
[TD]SI-V[/TD]
[TD]13.3%[/TD]
[TD]65.0%[/TD]
[TD]15.7%[/TD]
[TD]43.8%[/TD]
[TD]24.1%[/TD]
[TD]33.5%[/TD]
[TD]12.7%[/TD]
[TD]23.2%[/TD]
[/TR]
[TR]
[TD]END[/TD]
[TD]SI-D[/TD]
[TD]4.1%[/TD]
[TD]20.2%[/TD]
[TD]6.7%[/TD]
[TD]18.8%[/TD]
[TD]13.4%[/TD]
[TD]18.7%[/TD]
[TD]11.0%[/TD]
[TD]20.3%[/TD]
[/TR]
[TR]
[TD]TND[/TD]
[TD]TND-S&I[/TD]
[TD]2.0%[/TD]
[TD]9.8%[/TD]
[TD]5.4%[/TD]
[TD]15.1%[/TD]
[TD]10.6%[/TD]
[TD]14.8%[/TD]
[TD]5.5%[/TD]
[TD]10.1%[/TD]
[/TR]
[TR]
[TD]TND[/TD]
[TD]TND-MC[/TD]
[TD]1.0%[/TD]
[TD]4.9%[/TD]
[TD]8.0%[/TD]
[TD]22.3%[/TD]
[TD]5.7%[/TD]
[TD]7.9%[/TD]
[TD]3.5%[/TD]
[TD]6.4%[/TD]
[/TR]
</tbody>[/TABLE]

Cell A8 to E8 are dynamic and may be filled with as follows

A8 END
B8 SI-D
C8 WR
D8 SOI
E8 %Rg

I want a formula in cell F8 that will give me the result of 20.2%. It is captured based on all the five given criteria. if cell A8 is changed to TND and Cell B8 is changed to TND-MC then displayed result in Cell F8 should be 4.9%. For some reason I am unable to change the given format. So we have to stick to this format.


Thanks,
Imran.

 

[Matty]

Hi,

Looks like:

=INDEX(C4:J7,MATCH(1,IF(A4:A7=A8,IF(B4:B7=B8,1)),0),MATCH(1,IF(D1:J1=C8,IF(C2:J2=D8,IF(C3:J3=E8,1))),0))

Array entered, i.e. with CTRL+SHIFT+ENTER.

Matty

 

[imran1059]

Thanks a lot Matty ...... works perfect

 

[Matty]

Good to hear!