Indirect with Index match

[abhi2611]

All,

Attached is a spread sheet that has 5 tabs. The first one is the summary and the other 4 are the source tabs. Based on a few selections on the summary sheet, I am trying to pull information from the source tabs.

I'd appreciated any help on this.

Thanks for your time.

 

[Faseeh]

Make your data static and try this:

=OFFSET(INDIRECT($C$3&"!"&"B$5"),ROW(A1),MATCH($C$4,A!$C$5:$W$5,0))

Hopefully works.

 

[abhi2611]

Thank you Faseeh, I was able to get away with the below formula

=INDEX(INDIRECT("'"&$C$3 &"'!B5:W805"),MATCH(B7,INDIRECT("'"&$C$3&"'!B5:B805"),0),MATCH($C$4,INDIRECT("'"&$C$3&"'!B5:W5"),0))

Thanks again

 

[Ranjith kumar]

Hi abhi,
Try this formula also

=IF($C$3="A",HLOOKUP(Summary!$C$4,A!$C$5:$W$805,B7+1,FALSE),IF($C$3="B",HLOOKUP(Summary!$C$4,B!$C$5:$W$805,B7+1,FALSE),IF($C$3="C",HLOOKUP(Summary!$C$4,'C'!$C$5:$W$805,B7+1,FALSE),HLOOKUP(Summary!$C$4,D!$C$5:$W$805,B7+1,FALSE))))

 

[Faseeh]

@abhi2611,

I just tested my formula and it is working correctly. please replace your data created by randbetween() with static values and it will give correct results and don't worry about volatility of offset().