Index match on two criteria

[Caly]

Hi I have two sets of criteria and looking to match the answer based on that using index match. How can I do that?

Example
I have a chart in a separate tab with ranges for cost and then associated percent values based on categories

Col A. Col B. Col C. ColD. ColE
Seq. Cost Start. Cost End. Apple. Pear
1. $0.00. $2.00. 2%. 3%
2. $2.01. $5.00. 4%. 5%

I want to write a formula that will provide the percent values from columns D and E of the values match for Apple or Pear based on the cost of the item

So if for example I need to know the percent value for Apple if the cost is $2.10, I want the resulting answer to say 4%

I was trying to write the formula like this but getting a #ref value

=index($d$2:$e4, match(“apple”,$d$1:$e$1,0), match(“2.10”,$b$2:$b4,1))

 

[Caly]

This is the layout just using a few rows of data for each section

 

[Peter_SSs]

This is the layout just using a few rows of data for each section

Can you explain your new requirement again, referring specifically to that sample data?

 

[Caly]

I want to have a formula that will look at the different chart scenarios for the Dishware, Supplies, and then the chart that holds the data for Apple and Pear

Based on the cost for Pear for instance I want to bring in the Floor for Pear for the cost but looking at the different chart scenarios

I was trying an if statement but that did not work

=index($n$3:$o$4,
Match(b3,$l$3:$l$4,1),
Match(a3,$n$1:$o1,0),
If(a3<>”dishware”,index$e$3:$e$4,
Match(b3,$c$3:$c$4,1)),
Index($j:$j$4,
Match(b3,$h$3:$h$4,1))))

 

[Caly]

I want to have a formula that will look at the different chart scenarios for the Dishware, Supplies, and then the chart that holds the data for Apple and Pear

Based on the cost for Pear for instance I want to bring in the Floor for Pear for the cost but looking at the different chart scenarios

I was trying an if statement but that did not work

=index($n$3:$o$4,
Match(b3,$l$3:$l$4,1),
Match(a3,$n$1:$o1,0),
If(a3<>”dishware”,index$e$3:$e$4,
Match(b3,$c$3:$c$4,1)),
Index($j:$j$4,
Match(b3,$h$3:$h$4,1))))

This is the layout of the charts

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Dishware					Supplies							Apple	Pear		Type	Cost	Resulting floor based on the type and its cost	Written formula
2	Seq	Start	End	Floor		Seq	Start	End	Floor		Start	End	Floor	Floor		Pear	0.98	4%
3	1	0.00	2	10%		1	0.00	2	22%		0.00	19.99	2%	4%		Supplies	4	32%
4	2	2.01	4	12%		2	2.01	4	32%		20	49.99	3%	5%

 

[Caly]

I think I got it. I had the wrong column reference for the cost. Below is the formula im using but please advise if there is a simpler approach

=if(s3=“Pear”,index($n3:$o$4,
match(t3,$l$3:$l4,1),
Match(s3,$n$1:$1,0)),
=if(s3=“Apple”,index($n3:$o$4,
match(t3,$l$3:$l4,1),
Match(s3,$n$1:$1,0)),
If(s3<>”dishware”,
Index($j$3:$j$4,match(t3,$h$3:$h$4,1)),
Index($e$3:$e$4,match(t3,$c$3:$c$4,1))))

S3 = input to say the chart name such as Pear or Apple or Dishware or Supplies

T3 = cost input to use based on input from S3

C3 = Dishware cost section
E3 = dishware floor percents

H3 = supplies cost section
J3 = supplies floor percents

L3 = pear and apple cost section
N3 = apple floor percents
O3 = pear floor percents