XLOOKUP Display next non-zero reading columsn from top down.

Hi

I have two columns that display numbers at random intervals of time and otherwise are left as blanks or zeroes (unsure of the correct terminology). When a number appears on one of the columns, I want my formulas to search the other column for the most recent number and the next number.

The below formula is one that I got working, it tells me the most recent number because the second part of the array (B11), is the same row as the cell, and it returns the first value as I wanted it to.

=LOOKUP(1000,IF($B$1:$B11<>0,$B$1:$B11))

That method won't work when trying to find the next number, because I need excel to search from top to bottom and display the next number further down the column.

Here's one example of an XLOOKUP I tried, I made many attempts but couldn't find anything which worked. The problem is that XLOOKUP doesn't find the next result, and evaluates the entire array instead, to find the best match.

=XLOOKUP(10000,IF($B1:$B$36<>0,B1:$B$36),B1:$B$36,"nande",-1,1)

When the numbers occur is random but if Column C returns 109, Column B will always be 108.5 or 109.5, because it's always -0.5 or +0.5. If Column B returns 108.5, the next Column B number will be 107.5, 108.5 or 109.5, -1, +0 or +1.

Thus, it may be possible to have XLOOKUP search for a few exact numbers if that would solve the problem, though I'd prefer to just have XLOOKUP do what the LOOKUP is doing except with the top to bottom search function.

I intend not to have these be used as columns, but to stick them into a formula that compares whether the most recent and next Column B numbers were the same or different whenever Column C returns a value.

I don't have to use XLOOKUP, but I'd like to understand how to use XLOOKUP to do this assuming it's possible.

Thanks for any help and please feel free to ask any clarifying questions if needed.

Try this

=INDEX(B1:$B$36,MATCH(TRUE,B1:$B$36<>"",0))

Thanks Phuocam, it worked perfectly!

Can I ask, did you use index & match because it was the solution you thought of, or is it because XLOOKUP wouldn't have been able to do this?

I'm happy to use your suggestion, but I'd like to know if XLOOKUP could've done this and that I wasn't using it correctly, or whether XLOOKUP just wasn't the tool for the job.

Thanks Again.

Hey HansDouwe,

That worked as well.

I didn't think about using something like TRUE with exact match mode or using TRUE at all.

Thanks!