# Based on value, find next highest value in range and return it's adjacent cell

• In Cell B5 the user enters a dollar amount
• Cell B6 returns a value from a table
• The table contains dollar amounts in column B and a simple number in column A

Goal is to enter a dollar amount, and then find the dollar amount in the table is equal to or next highest and return the value in the adjacent cell in column A

Example: If user enters 23000 in B5, B6 will return 2

B5 - Input Dollar Amount

B6 - Return Tier Number from Table Below

A B

1 15000

2 25000

3 500000

4 100000

5 150000

## Files

• BTW. I am currently using the below formula and it returns the right value in column B, but for the life of me, nesting this in a VLOOKUP is not working to get the value in column A.

=SMALL(\$B\$2:\$B\$14,COUNTIF(\$B\$2:\$B\$14,"<"&B5)+1)

• There is probably an easier way

=INDEX(\$H\$8:\$H\$17,MATCH(INDEX(\$I\$8:\$I\$17,MATCH(TRUE,\$I\$8:\$I\$17>\$B\$5,0)),\$I\$8:\$I\$17,0))

To be committed with Ctrl+Shift+Enter as an array formula

• If you want use VLOOKUP then use this formula:

=VLOOKUP(SMALL(I8:I17,COUNTIF(I8:I17,"<"&B5)+1),CHOOSE({1\2},I8:I17,H8:H17),2,0)