# find last value before limit in unsorted list

#### hmmm...

##### Board Regular
Hi,

Let's say these cells contain these values:

cell a1=212
cell a2=324
cell a3=488
cell a4=538
cell a5=498
cell a6=444

I have a "threshold" value of 500.

I would like Excel to return the highest value that occurs before (in the array order) the threshold. (In this case the value would be 488). I need to keep the list unsorted. It would be best if I could find a formula (rather than VBA solution) for the sheet that I am using.

Any ideas? Thank you!

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### hmmm...

##### Board Regular
Actually, I am looking for the last value (in the array order) that occurs before (in the array order) the threshold value. My phrase "highest value" isn't necessay.

#### Rick Rothstein

##### MrExcel MVP
Actually, I am looking for the last value (in the array order) that occurs before (in the array order) the threshold value. My phrase "highest value" isn't necessay.
I think this formula will do what you want...

=INDEX(A:A,SUMPRODUCT(1*(A1:A6<500),1*(A2:A7>=500),ROW(A1:A6)))

##### MrExcel MVP
Looks like:
Rich (BB code):
``````=IFERROR(INDEX(A1:A6,MATCH(FALSE,IF(A1:A6<=C1,ROW(A1:A6)-ROW(A1)+1),0)-1),
INDEX(A1:A6,MATCH(MIN(ABS(A1:A6-C1)),ABS(A1:A6-C1),0)))
``````
which you need to confirm with control+shift+enter, not just enter.

#### Rick Rothstein

##### MrExcel MVP

I think this formula will do what you want...

=INDEX(A:A,SUMPRODUCT(1*(A1:A6<500),1*(A2:A7>=500),ROW(A1:A6)))
Forget the above formula.... it really does not work well. On the other hand, I believe this array-entered formula does work...

=INDEX(A:A,MIN(IF((A1:A8<500)*(A2:A9>=500),ROW(A1:A8))))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Note the second (middle) range is offset from the other two by one row... that is required to make the code work. So, when you adjust the code for your actual ranges, make sure that offset is preserved.

#### hmmm...

##### Board Regular
Aladin, Thank you for your reply. I expected I'd be able to decipher a reply better than I can yours. Your formula works for the example I gave, and I'll dissect it until I understand it, but that might take a while. Meanwhile, my data actually look like this:

cell a1=#N/A
cell a2=#N/A
cell a3=212
cell a4=#N/A
cell a5=#N/A
cell a6=#N/A
cell a8=324
cell a9=#N/A
cell a10=488
cell a11=#N/A
cell a12=#N/A
cell a13=538
cell a14=#N/A
cell a15=498
cell a16=#N/A
cell a18=#N/A
cell a19=444
cell a20=#N/A

I thought I'd be able to plug IFNA=FALSE into any formula in a reply, but apparently I should have put in this detail, since I can't see yet where I would plug this into your formula. Thanks!

#### pleeseemailme

##### Board Regular
Even if this would constitute a solution to the question under question (I doubt it), the suggested formula in the post you link to is not robust. This board also contains many kindered formulas without such flaw.

I'm not sure what I did to ensue such hostility...please clear this up for me so I don't make such a mistake again. I also don't understand what 'robust' means in the context above or to which flaw you refer in the formula. I do like the 'question under question' phrase. Let me know.

#### hmmm...

##### Board Regular
Rick, thank you for your reply too. Yours works on my first example too, of course; and again I'm surprised that I don't see yet how it does. Can it work with the #NAs in there? So far I can't figure that out. Thanks!

Replies
4
Views
140
Replies
4
Views
91
Replies
8
Views
92
Replies
5
Views
208
Replies
3
Views
331

1,147,818
Messages
5,743,382
Members
423,792
Latest member
travisds

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back