Index match based on criteria in multiple sections

[Caly]

I have two sets of criteria and looking to match the answer based on that using index match. But I also want to add an if statement so that if there is not a match to the header name specified in that chart area, that the formula will then return another answer for the result. 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%



But I have another data set not part of the criteria for the headers here of Apple and Pear. I’m trying to add in where if. There is not a match to Apple or Pear that another index match can be used to bring in data for another range. I tried writing it like the below but am getting a #N/A



=IFEEROR(MATCH(H2,$D$1:$E$1,0),

INDEX(D$2:E$3,MATCH(I2,B$2:B$3,1), MATCH(H2,D$1:E$1,0),”No”

 

[BSALV]

Map1
	A	B	C	D	E	F	G	H	I	J
1	seq	cost start	cost end	apple	pear			cost start	pear
2	1	0	2	2%	3%			2,01	5%
3	2	2,01	5	4%	5%
4
Blad2

Cell Formulas
Range	Formula
I2	I2	=INDEX($A$2:$E$3,MATCH(H2,$B$2:$B$3,0),MATCH(I1,$A$1:$E$1,0))

 

[Caly]

Thank you but I am getting an error for the answer. I have the below formula but still getting either #value! Or #n/a


Dishware
Col A. col B. col C. Col D.
row seq. Cost start. Cost end. Floor.
1. $0.00. $2. 30%
2. $2.01. $4. 30%
3. $4.01. $7.50 40%



Supplies
Col G. col H. col I. Col J
row seq. Cost start. Cost end. Floor.
1. $0.00. $2. 24%
2. $2.01. $4. 30%
3. $4.01. $7.50 55%


Col o. Col p. Col Q. Col R
Cost start. Cost end. Apple . Pear
$0.00. $19.99. 12%. 15%
$20.00. $50.00. 15%. 10%




=Index($Q$3:$R$7,match(b20,$O$3:$O$7,1),match(a20<>”dishware”,index($d$3:$d$12,match($b20,$b$3:$b$12,1)),index($j$3:$j$12,match($b20,$h$3:$h$12,1))))

 

[BSALV]

Caly

caly.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	dishware						Supplies						Hardware						Software
2	seq	cost start	cost end	apple	pear		seq	cost start	cost end	apple	pear		seq	cost start	cost end	apple	pear		seq	cost start	cost end	apple	pear
3	1	0	2	0.02	0.03		1	0	2	0.02	0.03		1	0	2	0.02	0.03		1	0	2	0.02	0.03
4	2	2.01	5	0.04	0.05		2	2.01	5	0.04	0.05		2	2.01	5	0.04	0.05		2	5	5	0.04	0.05
5
6			what	software	18
7			fruit	apple
8			cost Start	5
9
10			result	0.04
11
Blad1

Cell Formulas
Range	Formula
E6	E6	=MATCH(D6,1:1,0)-1
D10	D10	=INDEX(OFFSET($A$3:$E$4,,$E$6,,),MATCH(D8,OFFSET($B$3:$B$4,,$E$6,,),0),MATCH($D$7,OFFSET($A$2:$E$2,,$E$6,,),0))

 

[Caly]

Thank you. I have 3 separate charts each with different values. But I need to check the values to see what says pear for example and also if it says dishware to then bring in the floor value based on cost for that section or to bring in the floor value for supplies of the cell does not say pear or apple or dishwater or supplies. I tried entering offset but got #n/a

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%	=index($N$3;$O$4,match(c3,$l$3:$l4,1),match(b3,$n$1:$o$1,0),if(a3<>”dishware”,index($e$3:$$e$4,match($b3,$c$3:$c$4,1)),index($j$3:$j$4,match(b3,$h$3:$h4,1))))
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%

Where can the offset be added in the formula below
=index($N$3;$O$4,match(c3,$l$3:$l4,1),match(b3,$n$1:$o$1,0),if(a3<>”dishware”,index($e$3:$$e$4,match($b3,$c$3:$c$4,1)),index($j$3:$j$4,match(b3,$h$3:$h$4,1))))