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

$ 250,000.00	<<need to="" find="" the="" next="" value="" lower="" than="" this="" in="" entire="" range<="" td=""></need>
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>

 

[Eric W]

Welcome to the forum.

Would this work for you?

	A	B
1
2	250000	1263
3
4	Ton	Cost
5	N/A	N/A
6	N/A	N/A
7	N/A	N/A
8	N/A	N/A
9	N/A	N/A
10	N/A	N/A
11	N/A	N/A
12	100	$17,396
13	294	$51,184
14	488	$84,971
15	682	$118,759
16	875	$152,547
17	1,069	$186,334
18	1,263	$220,122
19	1,457	$253,910
20	1,651	$287,697
21	1,845	$321,485
22	2,039	$228,129
23	2,233	$249,825
24	2,427	$271,520
25	2,621	$293,216
26	2,814	$314,912
27	3,008	$336,608
28	3,202	$358,304
29	3,396	$380,000
30	3,590	$401,695
31	3,784	$423,391
32	3,978	$445,087
33	4,172	$357,899
34	4,366	$374,534
35	4,560	$391,169
36	4,754	$407,804
37	4,947	$424,439
38	5,141	$441,074
39	5,335	$457,709
40	5,529	$474,344
41	5,723	$490,979
42	5,917	$507,613
43	6,111	$436,741
44	6,305	$450,600
45	6,499	$464,458

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula B2 =LOOKUP(A2,B5:B45,A5:A45) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

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