Excel Formula: Lookup from multiple named ranges

Question

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?

Are you talking about "Excel tables", i.e. you created it using Insert->Table? Can you paste a link to a screenshot for better clarity? — Peter Albert, Feb 21 '13 at 07:54

score 1 · Accepted Answer · edited Jul 14 '15 at 17:21

1

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))

edited Jul 14 '15 at 17:21

Ram

3,092
10
40
56

answered Feb 21 '13 at 15:32

barry houdini

45,615
8
63
81

I was so close to this, I had tried using index with an offset but I was getting the syntax wrong. This is exactly what I was looking for.Thanks Heaps!!! – Benaiah Feb 22 '13 at 01:12

score 0 · Answer 2 · answered Feb 21 '13 at 08:52

0

Try this

=HLOOKUP(pickedcolref,
  IF(pickedtable=1,Table1,IF(pickedtable=2,Table2,IF(pickedtable=3,Table3,""))),
  MATCH(pickedrowref,
    OFFSET(
      IF(pickedtable=1,Table1,IF(pickedtable=2,Table2,IF(pickedtable=3,Table3,""))),
    0,0,,1)
  ,0)
 ,FALSE)

answered Feb 21 '13 at 08:52

chris neilsen

52,446
10
84
123

thanks, I didn't know how to use offset properly until I saw this post. – Benaiah Feb 22 '13 at 01:11

Excel Formula: Lookup from multiple named ranges

2 Answers2