Using IF with LOOKUP

**gally** · 12-29-2012, 05:02 PM

I was wondering if anybody was able to help I have attached a formula I need a hand with.

=LOOKUP($J$6,Calculations!$A$1:$A$100,Calculations!$H$1:$H$100)

If I wanted to add to this code that in Cell A1 for example there is either the value 1 or 2 - can I add an IF statement to the above code for example - IF cell A1 = 1 then as per the code above but if A1 =2 then the LOOKUP ranges would be different i.e. instead of....

Calculations!$A$1:$A$100,Calculations!$H$1:$H$100 it would be Calculations!$B$1:$B$100,Calculations!$I$1:$I$100

Is that to many arguments if so is there anyway I can work around it?

Thank you for looking

Gally

**Ace_XL** · 12-29-2012, 05:09 PM

Try

=LOOKUP($J$6,CHOOSE($A$1,Calculations!$A$1:$A$100,Calculations!$B$1:$B$100),CHOOSE($A$1,Calculations!$H$1:$H$100,Calculations!$I$1:$I$100))

or

=IF(A1=1,LOOKUP($J$6,Calculations!$A$1:$A$100,Calculations!$H$1:$H$100),IF(A1=2,LOOKUP($J$6,Calculations!$B$1:$B$100,Calculations!$I$1:$I$100),""))

**jeffreybrown** · 12-29-2012, 05:14 PM

How about

=CHOOSE(A1,
     LOOKUP($J$6,Calculations!$A$1:$A$100,Calculations!$H$1:$H$100),
     LOOKUP($J$6,Calculations!$B$1:$B$100,Calculations!$I$1:$I$100))

**gally** · 12-29-2012, 06:03 PM

Hi Ace_XL and Jeff - thank you both for taking the time to reply. I ended up using the second suggestion by Ace the =IF.

All the suggestions much appreciated though.

Kind Regards
Gally