What formula to use when looking up a value not in ascending order

[Craig4670]

Hello:

I have a question in regards to looking up a value from a list not in ascending order. I am not sure what formula(s) to use to return the value needed. I have tried using the index and match formula, but the result comes up as #NA. Any help would be appreciated.

Supplier	Yearly Spend	Top X Data SupplierX	Yearly SpendX
SupplierA	$1,552.00	SupplierV	1804
SupplierB	$1,564.00	SupplierU	1792
SupplierC	$1,642.00	SupplierT	1780
SupplierD	$1,588.00	SupplierS	1768
SupplierE	$1,600.00	SupplierR	1756
SupplierF	$1,612.00	SupplierQ	1744
SupplierG	$1,624.00	SupplierP	1732
SupplierH	$1,636.00	SupplierO	1720
SupplierI	$1,648.00	SupplierN	1708
SupplierJ	$1,660.00	SupplierM	1696
SupplierK	$1,672.00	SupplierL	1684
SupplierL	$1,684.00	SupplierK	#N/A
SupplierM	$1,696.00	SupplierJ	#N/A
SupplierN	$1,708.00	SupplierI	#N/A
SupplierO	$1,720.00	SupplierC	#N/A
SupplierP	$1,732.00	SupplierH	#N/A
SupplierQ	$1,744.00	SupplierG	#N/A
SupplierR	$1,756.00	SupplierF	#N/A
SupplierS	$1,768.00	SupplierE	#N/A
SupplierT	$1,780.00	SupplierD	#N/A
SupplierU	$1,792.00	SupplierB	#N/A
SupplierV	$1,804.00	SupplierA	#N/A

<tbody>
</tbody>

Thanks!

 

[kevatarvind]

try below in D2

=LARGE($B$2:$B$23,ROW()-1)

and i think your formula will too work u just need to Lock Range Using $ Sign

 

[dave3009]

Try making an exact match, assuming this is the method you are using.

=INDEX(B:B, MATCH(C2,A:A, 0))

 

[MrLookout9]

Are you talking about a vlookup? If so, be sure to lock in the lookup table by using F4 (ex $A$1:$B$10)

 

[kevatarvind]

can you please post your formula now what formula are you using ?

 

[Craig4670]

Thank you for your response. It did not work.

 

[Craig4670]

Thank you for your response. It did the same thing as the vlookup did. I still came out with #NA.

 

[Craig4670]

Yes, I was using a vlookup, but it does not work correctly if the data is not in ascending order.

 

[Craig4670]

I was using the vlookup formula, but it did not work when the data was not in ascending order.

 

[dave3009]

Try setting the vlookup to FALSE

=VLOOKUP(C2, $A$2:$B$23, 2, FALSE)