Nov 18 2021 08:41 PM
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?
=HLOOKUP(D20,CHOOSE({1,2,3},Sales_Range,Disc,Disc_Level),2,TRUE)
Right now i get an Error: #REF!
Nov 18 2021 11:06 PM
@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.
Nov 18 2021 11:18 PM
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.
Nov 19 2021 02:02 AM
@Martin_Weiss But you did use semi-colons between the curly brackets. @UncleGene used commas and that's why he got the #REF error.
Nov 19 2021 02:11 AM - edited Nov 19 2021 02:12 AM
@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 ;);)