Hi - Dave here.
Last week, I explained how to use XLOOKUP to find the
first negative value in a set of data.
This week, I have a related example that shows how to use XLOOKUP to find the first value based on
OR logic. The goal is to find the first "Red" or "Pink" record in the data. The formula looks like this:
=XLOOKUP(1,(data[Color]="red")+(data[Color]="pink"),data)
where "data" is an Excel Table in the range B5:E14. The double negative (--) converts TRUE and FALSE results to 1s and 0s. XLOOKUP then matches the first 1, and returns the corresponding row in the table:
[
download the workbook and read the full explanation]
The article provides a detailed explanation and includes the Excel workbook. I also explain how to use INDEX and MATCH to get the same result. Download the workbook and try it out yourself.
Excel formulas
We maintain a large list of working formulas
here.
If you need more structure, we also offer
video training.
Have a fun and safe weekend!
Dave
