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

[Land_Shark918]

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

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

 

[Eric W]

Welcome to the forum.

Would this work for you?

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]1263[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Ton[/TD]
[TD="bgcolor: #FAFAFA"]Cost[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]N/A[/TD]
[TD="bgcolor: #FAFAFA"]N/A[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]N/A[/TD]
[TD="bgcolor: #FAFAFA"]N/A[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]N/A[/TD]
[TD="bgcolor: #FAFAFA"]N/A[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]N/A[/TD]
[TD="bgcolor: #FAFAFA"]N/A[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]N/A[/TD]
[TD="bgcolor: #FAFAFA"]N/A[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]N/A[/TD]
[TD="bgcolor: #FAFAFA"]N/A[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]N/A[/TD]
[TD="bgcolor: #FAFAFA"]N/A[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"]100[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$17,396 [/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA, align: right"]294[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$51,184 [/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]488[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$84,971 [/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA, align: right"]682[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$118,759 [/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA, align: right"]875[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$152,547 [/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1,069[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$186,334 [/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1,263[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$220,122 [/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1,457[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$253,910 [/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1,651[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$287,697 [/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1,845[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$321,485 [/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2,039[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$228,129 [/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2,233[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$249,825 [/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2,427[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$271,520 [/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2,621[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$293,216 [/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2,814[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$314,912 [/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3,008[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$336,608 [/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3,202[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$358,304 [/TD]

[TD="align: center"]29[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3,396[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$380,000 [/TD]

[TD="align: center"]30[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3,590[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$401,695 [/TD]

[TD="align: center"]31[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3,784[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$423,391 [/TD]

[TD="align: center"]32[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3,978[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$445,087 [/TD]

[TD="align: center"]33[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4,172[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$357,899 [/TD]

[TD="align: center"]34[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4,366[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$374,534 [/TD]

[TD="align: center"]35[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4,560[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$391,169 [/TD]

[TD="align: center"]36[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4,754[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$407,804 [/TD]

[TD="align: center"]37[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4,947[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$424,439 [/TD]

[TD="align: center"]38[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5,141[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$441,074 [/TD]

[TD="align: center"]39[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5,335[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$457,709 [/TD]

[TD="align: center"]40[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5,529[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$474,344 [/TD]

[TD="align: center"]41[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5,723[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$490,979 [/TD]

[TD="align: center"]42[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5,917[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$507,613 [/TD]

[TD="align: center"]43[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6,111[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$436,741 [/TD]

[TD="align: center"]44[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6,305[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$450,600 [/TD]

[TD="align: center"]45[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6,499[/TD]
[TD="bgcolor: #FAFAFA, align: right"]$464,458 [/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=LOOKUP(A2,B5:B45,A5:A45)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[etaf]

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