Calculating a goal based on where a value falls between two other values

[adflamm7]

Sorry, if the title doesn't make sense. Here goes:

I have Sales per labor goals tiered as 3 Star, 4 Star, and 5 Star labor goals. Sales per labor goals change based on the sales number. So, basically I want to say:

If their actual sales is between these numbers (sales bracket start and end), then this is their 3 star, 4 star, and 5 star goals for that sales bracket. And then return in another column, what tier they were ("3 Star") based on the actual results for the week

I'm sorry if I've butchered the explanation of this, I am attaching an example

https://drive.google.com/file/d/15n-t6AidxedKcesBCjIVGdBTYa2l5Jpv/view?usp=sharing

Thank You very much for any help.

 

[Oaktree]

Welcome to the board.

The upper end of the range is not needed...you can use VLOOKUP if the lower bound for each sales goal is in the left column of your range.

Try =VLOOKUP($B2,$I:$M,3) for the 3 star and change the ",3" to ",4" and ",5" for 4 and 5 stars respectively.

 

[adflamm7]

Welcome to the board.

The upper end of the range is not needed...you can use VLOOKUP if the lower bound for each sales goal is in the left column of your range.

Try =VLOOKUP($B2,$I:$M,3) for the 3 star and change the ",3" to ",4" and ",5" for 4 and 5 stars respectively.

Thank you so much for your reply. Let me try and elaborate a little better.

In the Columns labeled 3 Star Goal, 4 Star Goal, 5 Star Goal, I would like it to show the value from the table on the right, based off their sales.

So, for Store 1510, they did $14000 in sales, I would like to pull the 3 Star, 4 Star, and 5 Star goals from the right side ($34.61, $36.31, $38.00) and those should fill the appropriate columns.

The SPLH column should be measured against those 3 columns and in the column labeled "Results." I would like it to return whether it was a 3 Star value, 4 Star Value, or 5 Star Value.

I hope I explained that well, and I'm sorry if I'm asking for a function that isn't possible. Thank you so much for even looking.

I've copied the formula you posted into the results column and it's returning 35.9863, it's totally possible I'm doing something wrong.

 

[adflamm7]

Oak Tree, I figured out what I was doing wrong, Thank You so Much

 

[adflamm7]

I placed each VLookup in it's appropriate column, Is it possible to return in the column "Results" which Goal was Met. For instance, the SPLH column for Store 1526 is 41.15, which is above the 4 Star goal (but under the 5 Star Goal) I would like the results column to say "4 Star" for that store. Sorry for all the replies, I'm trying to find where to delete replies

 

[Oaktree]

Try G2=INDEX($K$1:$M$1,MATCH($C2,$D2:$F2)), copied right and down.

 

[adflamm7]

Try G2=INDEX($K$1:$M$1,MATCH($C2,$D2:$F2)), copied right and down.

Perfect, Thank You so much!!!