Is it possible to use HLOOKUP and CHOOSE to get my answers from different rows?

Occasional Visitor

I have been meaning to ask if this formula works? 
AND if not what is the alternative if i stil wan to stick with using HLOOKUP?

UncleGene_0-1637296730094.png

=HLOOKUP(D20,CHOOSE({1,2,3},Sales_Range,Disc,Disc_Level),2,TRUE)

Right now i get an Error: #REF!

 

4 Replies

@UncleGene Try it this way:

=HLOOKUP(D20,CHOOSE({1;2},Sales_Range,Disc_Level),2,TRUE)

Use a semicolon in the "CHOOSE list" for a horizontal lookup, and a comma for a vertical lookup.

 

Hi @UncleGene 

 

I do not see any mistake in your formula. I rebuilt the example based on your screenshot and it works fine for me. Attached please find my file, so you could compare with yours.

 

Hope this helps.

@DTE But you did use semi-colons between the curly brackets. @UncleGene used commas and that's why he got the #REF error.

@Riny_van_Eekelen,
yes, you are right. I did not notice the difference because I work on a German version, where I have to use semi-colons anyway