# Match excel find the largest value in a range given a speciic value

#### Land_Shark918

##### New Member
I am trying to have excel find the largest "Cost" value in a given range that is lower than a specified Cost value. I then need it to display its corresponding "Ton" value. Problem is that i need it to look the value up in reverse order, in order o have it select the absolute lares value in the entire table. I've looked all over, and seen multiple "solutions", but haven't had one work yet. See below for reference

Nick

 \$ 250,000.00 < Ton Cost N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A 100 \$ 17,396 294 \$ 51,184 488 \$ 84,971 682 \$ 118,759 875 \$ 152,547 1,069 \$ 186,334 1,263 \$ 220,122 1,457 \$ 253,910 1,651 \$ 287,697 1,845 \$ 321,485 2,039 \$ 228,129 2,233 \$ 249,825 2,427 \$ 271,520 2,621 \$ 293,216 2,814 \$ 314,912 3,008 \$ 336,608 3,202 \$ 358,304 3,396 \$ 380,000 3,590 \$ 401,695 3,784 \$ 423,391 3,978 \$ 445,087 4,172 \$ 357,899 4,366 \$ 374,534 4,560 \$ 391,169 4,754 \$ 407,804 4,947 \$ 424,439 5,141 \$ 441,074 5,335 \$ 457,709 5,529 \$ 474,344 5,723 \$ 490,979 5,917 \$ 507,613 6,111 \$ 436,741 6,305 \$ 450,600 6,499 \$ 464,458

<tbody>
</tbody>

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Eric W

##### MrExcel MVP
Welcome to the forum.

Would this work for you?

AB
1
22500001263
3
4TonCost
5N/AN/A
6N/AN/A
7N/AN/A
8N/AN/A
9N/AN/A
10N/AN/A
11N/AN/A
12100\$17,396
13294\$51,184
14488\$84,971
15682\$118,759
16875\$152,547
171,069\$186,334
181,263\$220,122
191,457\$253,910
201,651\$287,697
211,845\$321,485
222,039\$228,129
232,233\$249,825
242,427\$271,520
252,621\$293,216
262,814\$314,912
273,008\$336,608
283,202\$358,304
293,396\$380,000
303,590\$401,695
313,784\$423,391
323,978\$445,087
334,172\$357,899
344,366\$374,534
354,560\$391,169
364,754\$407,804
374,947\$424,439
385,141\$441,074
395,335\$457,709
405,529\$474,344
415,723\$490,979
425,917\$507,613
436,111\$436,741
446,305\$450,600
456,499\$464,458

</tbody>
Sheet7

Worksheet Formulas
CellFormula
B2=LOOKUP(A2,B5:B45,A5:A45)

</tbody>

<tbody>
</tbody>

#### etaf

##### Well-known Member
i need it to look the value up in reverse order, in order o have it select the absolute lares value in the entire table.
what exactly does this mean ????

if you use index/match and you can lookup the lower value

=index(Tons range to return , match( value to lookup , Range of values , 1 ))
1 lookups the nearest lower value

so assuming the lookup was in A2 and the range table was

A10:B200

=Index( A10:A100, match(A2, B10:B100, 1))

1,190,851
Messages
5,983,229
Members
439,832
Latest member
Kephart87

### 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