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

jandress

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?

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!

jandress

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

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

https://app.smartsheet.com/b/publish?EQBCT=3f38c529e7b54832808079457a30e54d

Hope this helps!

Note

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

https://community.smartsheet.com/discussion/64051/vlookup-for-a-range-of-numbers

jandress

@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.