Find First Value in Column Which is Greater Than Specified Value (Searching from Last Value in Column)

[blogan418]

I'm trying to find the first value in a column which is greater than a specified number (searching from the bottom of the column). Once that is found, I need it to return data (a date) from a corresponding cell. Because of the dataset, the search must start at the bottom of the column. I've tried using xlookup, but can't get it to work properly.

 

[ER_Neha]

Please clarify your second requirement

 

[Fluff]

Hi & welcome to MrExcel.
What is the formula that you have?

 

[blogan418]

Please clarify your second requirement

Sure. 2nd requirement is to return the data from corresponding row in Column A. For example, I'm looking for the first value in Column B (searching from the bottom of the column) which is greater than the "base value" of 610.00. When that is located, I want the formula to return the date on the same row from Column A.

In the example, the first value greater than 610.00 (searching from the bottom of Column B) is 614.99. I'd like the formula to return the date from that same row, which is 7/23/2008.

 

[blogan418]

Hi & welcome to MrExcel.
What is the formula that you have?

Hi - The formula I was trying to work with was:

=XLOOKUP(D14,B1:B12,A1:A12,,1,-1)

But, that returns cell A3

 

[Fluff]

That formula should work, in what way isn't it?

 

[blogan418]

That formula should work, in what way isn't it?

That formula pulls the date which corresponds to the closest actual value which is greater than or equal to 610.00 (no matter where it is in the order of the column). What I need is date which corresponds with the first amount (searching from the bottom) which is greater than or equal to 610.00. That would be A5.

 

[Fluff]

Ok, how about

=LOOKUP(2,1/(B1:B12>=D14),A1:A12)

 

[blogan418]

Ok, how about

=LOOKUP(2,1/(B1:B12>=D14),A1:A12)

That works. Thanks!!

 

[Fluff]

You're welcome & thanks for the feedback.