Vlookup with Find/Search/Cell address

[Svmaxcel]

I am attaching screenshot of Dummy data.

A5:E11 has the data like Name/Sales/Revenue...

I created Data validation in A17, so that only Names can be selected from the list.

Below Name I have the metrics like Sales/Revenue/Margin...

I want that when I select the Name in A17, Sales/Revenue/Margin should Automatically pick up values from the Data table.

I tried Vlookup(A19, B5:C11, 2, 0) and I can get the values without issues.

In the table A5:E11, Name field remains constant however Metrics like Sales/Revenue/Margin column might change, I mean Sales could be Column C or might be D.

So I dont want to specify the Column number in Vlookup, instead I want that excel should check the Sales column and give me Output.

I thought of using Named Ranges but didnt work out as excel doesnt know in which column to search Vlookup(A19,Table1, Sales,0)
So I tried Vlookup(A19,Table1,Find(),0) But was unable to complete formula.

Can we use Vlookup,Find with Cell (Address) to get the output

 

[Chihiro]

Upload sample workbook along with expected result please.
It would make it so much easier to help you.

 

[Haz]

=INDEX($5:$11, MATCH($B$17, INDEX($5:$11, 0, MATCH("Name", $5:$5, 0)), 0), MATCH(A19, $5:$5, 0))

Copy and paste for B21 and B23. Their positions won't matter as long as the names in column A and line 5 match.

 

[bosco_yip]

Or,

B19, copied down :

=IF(A19="","",VLOOKUP(B$17,B$6:E$11,MATCH(A19,B$5:E$5,0)))

Regards
Bosco