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

  • 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