Execute a Hlookup inside an IF statement

[rjwalters]

Can I execute a formula inside a formula.


My colleague is want to to do this:


If he selects fruit, he wants his hlookup to return for the selected hlookup for fruit, but if he selects meat, he wants his hlookup to return the selected hlookup for meat.


Example =IF(J4="Fruit",(=HLOOKUP($J$4,Fruit!$B$1:$B$15,2,)IF J4="Meat",(=HLOOKUP($J$4,Meat!$B$1:$B$15,2,))"

 

[NARAYANK991]

Hi ,


Two points :


1. The HLOOKUP should be over a horizontal range e.g. B1:H1 , not B1:B15 ; for the latter , the right lookup function would be VLOOKUP.


2. When you use a third parameter of 2 ( HLOOKUP($J$4,Fruit!$B$1:$B$15,2,) ) , it means return the value from the second column / row of the range corresponding to the matched value from the first column / row ; defining the second parameter as a single column / row ( Fruit!$B$1:$B$15 ) will generate an error.


You can use either of the following :


HLOOKUP($J$4,Fruit!$B$1:$H$15,2,)


The above function will check in the first row , within the range B1:H1 to see whether the value in J4 occurs anywhere ; suppose it occurs in G1 ; the formula will then return the value from G2.


VLOOKUP($J$4,Fruit!$B$1:$H$15,2,)


The above function will check in column B , within the range B1:B15 to see whether the value in J4 occurs anywhere ; suppose it occurs in B7 ; the formula will then return the value from C7.


Narayan

 

[rjwalters]

But can we use this inside an if statement. If this do this vlookup, else if this do this vlookup?

 

[rjwalters]

So after re reading I see better, when we return fruit, it should return anything in the fruit column. So the column is labeled fruit and in that column is apples, bananas, peaches. It would return this if the user selects fruit, but if the user selects meat, it would return pork, steak, chicken.


Did that make it clear as mud>>>>>

 

[NARAYANK991]

Hi ,


You are right , but the way you have posted your formula initially Fruit and Meat are worksheet tabs ; if really they are named ranges , then the formula would be :


=IF($J$4="Fruit",VLOOKUP($J$4,Fruit,2,False),IF($J$4="Meat",VLOOKUP($J$4,Meat,2,False))


where Fruit may be the range C7:C10 , while Meat may be the range D7:D11. I assume that J4 will not take any value other than Fruit and Meat.


Narayan

 

[rjwalters]

Ok have the first part working, the second command returns a n/a.

 

[Luke M]

If your table looks something like this:

[pre]

Lookup	Fruit		Meat
this	apple		pork
that	orange		beef
some	banana		chicken
a lot	soda		Luke M
just 2	Carlsberg	b(ut)ob(ut)hc

[/pre]
Then formula could be:

=VLOOKUP(J5,A1:C4,MATCH(J4,A1:C1,0),FALSE)


This way, the MATCH function determines which column to look at in the overall table. I'm also assuming that the lookup value is not the same cell with fruit/meat. Otherwise, there's no need for a lookup - with only 2 values, there's only 2 possible responses.

 

[jeffreyweir]

Sounds like dynamic dependant dropdowns is what's called for.

See https://www.dropbox.com/s/ueqf2ewc6a3rx4y/Dynamic%20Dependent%20dropdowns%2020130628.xlsm for an example that uses Excel tables to create cascading dropdowns of as many unique levels as you like.


Note that this uses the non-volatile INDEX function (whereas most approaches use Volatile OFFSET or INDIRECT functions, which is not great if you've got big workbooks).


This file also has some demo code in it to clear 'downstream' choices if upstream choices change.


You can turn the code on or off, or even delete it entirely ...it's just there for informative purposes.