Search code examples
excelexcel-formulaexcel-indirectexcel-match

Excel Formula: Lookup from multiple named ranges

I have 3 Tables stored as named ranges.
The user picks which range to search through using a drop down box. The named ranges are Table1, Table2 and Table 3.

Table1

    0.7     0.8     0.9
50  1.08    1.06    1.04
70  1.08    1.06    1.05
95  1.08    1.07    1.05
120 1.09    1.07    1.05

Table2

    0.7     0.8     0.9
16  1.06    1.04    1.03
25  1.06    1.05    1.03
35  1.06    1.05    1.03

Table 3

    0.7     0.8     0.9
50  1.21    1.16    1.11
70  1.22    1.16    1.12
95  1.22    1.16    1.12
120 1.22    1.16    1.12

Then they pick a value from the header row, and a value from the first column.
i.e. the user picks, Table3, 0.8 and 95. My formula should return 1.16.

I am halfway there using indirect (table1), however I need to extract the header row, and first column so I can use something like 

=INDEX(INDIRECT(pickedtable),MATCH(picked colref,INDIRECT(pickedtable:1)), MATCH(picked rowref,INDIRECT(1:pickedtable)))

Any idea how to achieve this?

Solution

  • INDIRECT(pickedtable) should work OK to get the table but to get first column or row from the table you can use INDEX with that, so following your original approach this formula should work

    =INDEX(INDIRECT(pickedtable),MATCH(pickedcolref,INDEX(INDIRECT(pickedtable),0,1),0),MATCH(pickedrowref,INDEX(INDIRECT(pickedtable),1,0),0))

    or you can use HLOOKUP or VLOOKUP to shorten as per chris neilsen's approach, e.g. with VLOOKUP

    =VLOOKUP(pickedcolref,INDIRECT(pickedtable),MATCH(pickedrowref,INDEX(INDIRECT(pickedtable),1,0),0))