Hi - Dave here.
I had a question recently about how to lookup the
first negative value in a set of data. As usual, there are several ways to approach the problem in Excel.
One nice solution is to use XLOOKUP like this:
=XLOOKUP(1,--(data[Low]<0),data)
where "data" is an Excel Table in the range B5:C16. 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
