Lookup 2 vertical and 1 horizontal

[kashyap]

Hi, I have data's(All text) from Column A to Column G and I need a formula which lookup ColA, ColB, Row1 and gives output

ColA ColB ColC ColD ColE ColF ColG
Scenario Op NC FO FI CP Past
What is.. ABC kjha ert sd fgh fg
What is.. XYZ qwer ert df fgh aw
What is.. MNO er rt ter er
What is.. EFG ert er ghj ytry
You have.. ABC
You have.. XYZ
You have.. MNO
You have.. EFG
Where.. ABC
Where.. XYZ
Where.. MNO
Where.. EFG

 

[Aladin Akyurek]

Care to provide what value(s) you want to look up?

 

[kashyap]

Actually user can select Scenario & Op which I want to lookup vertically which is in Column A & B and user can also select "NC, FO, FI, CP, Past" as well which is need to lookup horizontal which is in Column C to G.

I tried with index and match functions, but here i can do only 2 lookup and not the 3rd one..

 

[Aladin Akyurek]

Actually user can select Scenario & Op which I want to lookup vertically which is in Column A & B and user can also select "NC, FO, FI, CP, Past" as well which is need to lookup horizontal which is in Column C to G.

I tried with index and match functions, but here i can do only 2 lookup and not the 3rd one..

Control+shift+enter, not just enter...

=INDEX($C$2:$G$100,
    MATCH(1,IF($A$2:$A$100=Scenario,IF($B$2:$B$100=Op,1)),0),
    MATCH(Header,$C$1:$G$1,0))

 

[kashyap]

Hi Aladin, This is not giving me the desired output.. I tried with the below formula as well, but not resired result

=INDEX(PRX!A1:H111,MATCH(1,IF(PRX!A2:A111=Reference!J9,IF(PRX!C2:C111=Reference!F5,1)),0),MATCH(Reference!K6,PRX!A1:H1,0))
Control+shift+enter

I need to index A:H
match J9 with A:A
match F5 with C:C
match H1 with A1:H1

 

[Aladin Akyurek]

Hi Aladin, This is not giving me the desired output.. I tried with the below formula as well, but not resired result

=INDEX(PRX!A1:H111,MATCH(1,IF(PRX!A2:A111=Reference!J9,IF(PRX!C2:C111=Reference!F5,1)),0),MATCH(Reference!K6,PRX!A1:H1,0))
Control+shift+enter

I need to index A:H
match J9 with A:A
match F5 with C:C
match H1 with A1:H1

The info is still not complete... I'm trying to guess the data area, that is, correct ranges...

=INDEX(PRX!$D$2:$H$111,
   MATCH(1,IF(PRX!$A$2:$A$111=Reference!J9,
                IF(PRX!$C$2:$C$111=Reference!F5,1)),0),
   MATCH(Reference!K6,PRX!$D$1:$H$1,0))