Help - Populate a value from another sheet based on a range

Sheet 1 contains all of my costs for a client

Cost column

$15,500

$1,500

$3,500

Sheet 2 contains columns with the range of costs and a specific value for that range (3 columns)

Examples:

$0 $1,500 1

$1,500 $5,000 2

$5,000 $10,000 3

$10,000 $20,000 4

To create a Report I want to pull back the value for the range (1, 2, 3, 4) to a new column (Cost value) in Sheet 1.

I am struggling with the formula and I have attempted VLookup as well as Index. Thoughts on a good way to approach this?

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭

    Hello @jandress,

    To clarify, you want to get Column 1 and Column 2 from Sheet 2 and put them into Sheet 1? I'm not sure if you want these to go into a single column or into two separate columns, I will assume they should go into separate columns (if you want them in one you can do this with a formula).

    I would use INDEX(MATCH()) in Sheet 1 and reference what I assuming is the Specific Value column (the third column of Sheet 2).

    In Sheet 1 add the Specific Value column and use this formula to get Column 1 (the lower end of the range in Sheet 2).

    =INDEX({Column 1 in Sheet 2}, MATCH([Specific Value]@row, {Specific Value in Sheet 2}, 1))

    Note that if your Specific Value in Sheet 2 is sorted descending the 1 at the end of the formula will need to be -1. If it is not sorted at all it needs to be changed to 0.

    Repeat this for column 2

    =INDEX({Column 2 in Sheet 2}, MATCH([Specific Value]@row, {Specific Value in Sheet 2}, 1))

    Hope this helps!

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

  • Hello @Dan Palenchar - Thank you so much for assisting!

    Let me see if I can verify what I am attempting to accomplish. I have a static value in sheet 1/column 5 "Spend" $3,000. In sheet 2 I have a range of dollar values min/max in columns 1 and 2, and then a numeric value assigned to the range in column 3. I am attempting to find which range the dollar amount from sheet 1 falls into, and then assign it the numeric value from sheet 2/column3. The assigned numeric value will go in a new column of sheet 1 ("Spend Range") Does that make sense?

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭

    Hey @jandress,

    Happy to help! Yes, this can be done, see below!

    Solution

    So you have:

    • Sheet 1:
      • Spend column w/ a value of $3,000
      • Spend Value that should take in the appropriate value in from Sheet 2
    • Sheet 2:
      • Min (of range for a Spend Value)
      • Max
      • Spend Value

    So yes, you can do this. First off, your ranges need to be mutually exclusive. For example, you have $1,500 as the Max of Range Value 1 AND the Min of Range Value 2. So I set it up like this to remove the overlap.

    Then I used this formula in Sheet 1 to return the highest spend value where the Cost in Sheet 1 is greater than or equal the Min in Sheet 2.

    =IF(ISNUMBER(Cost@row), INDEX({SpendRange}, MATCH(MAX(COLLECT({Min}, {Min}, @cell <= Cost@row)), {Min}, 0)), "")

    It ends up looking like this

    You can see this all together and interact with it via a Published Dashboard here and below

    Hope this helps!

    Note

    Credit to @Paul Newcome, I got this idea from his solution in the post below.

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

  • @Dan Palenchar - Really appreciate your assistance here! I think I am almost there. I am receiving an "#Invalid Column Value" error. I have verified all of the columns that are being used are Text/Number, and I am not sure why I am receiving this.

    For this value "{SpendRange}" - I referenced (referenced from another sheet/highlighting) all cells from Column SpendRange row 1 through column Max row 5

    For this value "{Min}" - I referenced all cells in the Min column

    Let me know if you have any ideas here, and again thank you for your assistance.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!