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

#### Craig4670

##### Board Regular
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!

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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

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

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

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

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

Thank you for your response. It did not work.

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

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

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

Try setting the vlookup to FALSE

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

1,196,131
Messages
6,013,633
Members
441,777
Latest member

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