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.


  • There is probably an easier way


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