Pull data from table using multiple criteria

[Viking1221]

Hello,

My colleagues and I are are stumped on a formula we are trying to build to pull in the correct assumptions based on multiple criteria across rows and columns. I attached a sample spreadsheet of what we are trying to do. We want to pull in the correct number that corresponds to the table. So for this example if we have a dollar $ between the MID and MAX amounts in the columns of $1,100 and a corresponding % variance of 10% (between the -5 and -15%) we want the formula to extract the 35%.

So far all our attempts have failed and we do not want to do this via a macro, but want a formula to do the work.

If anyone has any ideas or knows how to the this please share.

I have a sample version of a file located here: Sample

Thanks

 

[oldbrewer]

I never download just copy a few rows and paste in the reply box

 

[Viking1221]

I never download just copy a few rows and paste in the reply box

Here is a sample below. In this example, we have a $ variance of $1,100 and a % variance of -10%. So we need to pull in the -30% since $1,100 is between $1,001 and $1,250 and a Variance of -10% which is between -5% and -15%

Min	Max	-5%	-15%	-25%	-40%
0	250	0.0%	0.0%	0.0%	0.0%
251	500	0.0%	0.0%	0.0%	-5.0%
501	750	-10.0%	-10.0%	-10.0%	-15.0%
751	1,000	-20.0%	-20.0%	-20.0%	-25.0%
1,001	1,250	-30.0%	-30.0%	-30.0%	-50.0%
1,251	1,500	-50.0%	-50.0%	-50.0%	-75.0%
1,501	1,750	-75.0%	-75.0%	-75.0%	-75.0%

<tbody>
</tbody><colgroup><col><col span="5"></colgroup>

 

[Marcelo Branco]

Welcome to Mr Excel

Question
What are the expected results for
1100 and -4%
1100 and -30%

M.

 

[Viking1221]

for 1100 and -4% the expected result = -30%. 1100 and -30% result = -50%

 

[Marcelo Branco]

Try

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Min​	Max​	-5%​	-15%​	-25%​	-40%​		Crit1​	Crit2​	Result​
2​	0​	250​	0,00%​	0,00%​	0,00%​	0,00%​		1100​	-10%​	-30,00%​
3​	251​	500​	0,00%​	0,00%​	0,00%​	-5,00%​		1100​	-4%​	-30,00%​
4​	501​	750​	-10,00%​	-10,00%​	-10,00%​	-15,00%​		1100​	-30%​	-50,00%​
5​	751​	1000​	-20,00%​	-20,00%​	-20,00%​	-25,00%​
6​	1001​	1250​	-30,00%​	-30,00%​	-30,00%​	-50,00%​
7​	1251​	1500​	-50,00%​	-50,00%​	-50,00%​	-75,00%​
8​	1501​	1750​	-75,00%​	-75,00%​	-75,00%​	-75,00%​

<tbody>
</tbody>

Criteria in columns H:I

Formula in J2 copied down
=INDEX($C$2:$F$8,MATCH(H2,$A$2:$A$8),IFERROR(MATCH(I2,$C$1:$F$1,-1)+1,1))

Hope this helps

M.

 

[Marcelo Branco]

oops...the formula needs a small adjustment (to handle cases where there is an exact match in C1:F1)

New version
=INDEX($C$2:$F$8,MATCH(H2,$A$2:$A$8),IFERROR(MATCH(I2,$C$1:$F$1,-1)+1*ISNA(MATCH(I2,$C$1:$F$1,0)),1))

M.

 

[Viking1221]

This works perfectly. Thanks

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.