Hi - Dave here.
Happy Friday!
One of XLOOKUP's nice benefits is its ability to work with vertical or horizontal data. The syntax used for horizontal lookups is the same as for vertical lookups.
In the past, you would typically use VLOOKUP for
vertical data and HLOOKUP for
horizontal data. But XLOOKUP makes this choice unnecessary.
For example, in the worksheet below, we want to determine the right discount for a given quantity. Because the table is horizontal, the "old" solution is based on HLOOKUP and a formula like this:
=HLOOKUP(B5,data,2,TRUE)
With XLOOKUP, we don't need to consider whether the data is horizontal or vertical. We can simply use XLOOKUP like this:
=
XLOOKUP(B5,quantity,discount,,-1)
You can see the XLOOKUP formula below:
[
Download the workbook and read the full explanation]
Naturally, you can also solve this problem with INDEX and MATCH, the Swiss Army Knife of lookup formulas :) I've included all three formulas on different sheets. Download the workbook and try it out yourself.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I review one useful Excel formula in detail. You can sign up on our home page.