Hi - Dave here.
Happy Friday!
A couple of months ago, I published an article on
XLOOKUP vs INDEX and MATCH.
One of the pros I mentioned for INDEX and MATCH is that MATCH uses numbers for columns and rows, which means these numbers can be easily modified in the formula.
You can see an example of this in the worksheet below, where the goal is to retrieve both the width and the length in a lookup operation.
The first INDEX and MATCH formula gets the width. To get the length, we start with the same formula, then add 1 to the column number:
[
Click here to read the article and download the workbook]
Can you do this with XLOOKUP? Yes, but it is a bit awkward, and I think INDEX and MATCH is a better approach. Click the link above to read a full explanation, including an XLOOKUP formula. Then download the worksheet 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
