Formula needs to change based on a cell value

[rutho13]

Hi all,

I am trying to write a formula that uses data from a table, however there are multiple tables, and the correct one to be used is based on what is entered in a specific cell. I have attached the example worksheet - I have the basic formula in cell D28, and my attempt at a more complex version in cell D32, but I don't know how to extend it to cover all four different data tables without using a load of IF functions. Maybe some sort of LOOKUP function? Any help would be appreciated.

Thanks,

Ruth

[AliGW]

OK - this can be done easily, as you already have named ranges (tables). If you are happy to create two hidden cells (let's say N8 and N9, then hide the column), we can do this:

N8: =LOOKUP(Limit,{200,220,240,250},{"table_200_pa","table_200_pa","table_200_pa","table_200_pa"})
N9: =LOOKUP(Limit,{200,220,240,250},{"flow_200_pa","flow_200_pa","flow_200_pa","flow_200_pa"})

Then in D28:

=INDEX(INDIRECT(N8),MATCH(C28,INDIRECT(N9),-1),2)

[ChemistB]

I used INDEX's rarely used optional 4th argument for "Area"

First I created a named range called MyTables
(just selecting all the ranges while holding down the control key) Then going to Define Name

In D25, with the value in C25

=INDEX(MyTables, MATCH(C25, INDEX(MyTables,,1,MATCH(C8, {200,220,240,250},0)),-1),2,MATCH(C8, {200,220,240,250},0))