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

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?

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!
 Sheet 1:

@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 5For 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
Categories
Check out the Formula Handbook template!