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!