Need Help to Make Formula Which Gives value based on criteria both in row and column

[milanpatel17]

Hello,

I am trying to write a formula which gives value based on two criteria, one in row and one in column. The row title remains in same cell but the row title may come in different cell. How to get the value even if the column title move in other cell?

I can use simple vlookup or Hlookup but what in case the Male column shifts to female column and vice versa. I am using this formula but getting #N/A:
=INDEX($B$1:$C$7,MATCH($G2&H$1,INDEX($A$2:$A$7&$B$1:$C$1,),0))
What formula shall I use to get the value in table 2 from table 1.
Table 1 Table 2
[TABLE="width: 576"]
<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Male[/TD]
[TD="width: 64"]Female[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Female[/TD]
[TD="width: 64"]Male[/TD]
[/TR]
[TR]
[TD]Price[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Price[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Age[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Age[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Income[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Income[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Home[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Home[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Married[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Married[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Single[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Single[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Your assistance in this regard will be highly appriciated.

Thanks in advance
Milan

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

Try this formula in H2
=INDEX($B$2:$C$7,MATCH($G2,$A$2:$A$7,0),MATCH(H$1,$B$1:$C$1,0))
copy across to I2 and down

M.

 

[milanpatel17]

Hi, Marcelo

Thank you so much.Sorry for late reply as i was busy in work.
Your help matters very much to me. You made my work very easy.

Regards,
Milan Patel

 

[Marcelo Branco]

Hi, Marcelo

Thank you so much.Sorry for late reply as i was busy in work.
Your help matters very much to me. You made my work very easy.

Regards,
Milan Patel

Hi Milan,

You are very welcome and thanks for the feedback.

M.