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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!