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))

 

[Natas]

I think I understand based on your formula you provided. It is row, then column.

=INDEX(A1:D3,MATCH(2.01,B1:B3,0),MATCH("Apple",$A$1:$E$1,0))

 

[Peter_SSs]

As @Natas has stated row comes first so your matches are the wrong way around.
However, you are correct in that the cost match should not be an exact match.
Also, you should not have quote marks around the cost value.

22 05 25.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Seq.	Cost Start.	Cost End.	Apple	Pear			Item	Cost	%
2	1	0.00	2.00	2%	3%			Apple	2.10	4%
3	2	2.01	5.00	4%	5%			Pear	0.55	3%
4								Pear	125.00	5%
Index Match

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

 

[Caly]

Thank you both so much. This worked perfectly

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[Caly]

Hi again I need to add to this to include a result for if there is 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”

 

[Peter_SSs]

What about some sample data and expected results showing the new arrangement/requirement and explain again in relation to that sample data?
Please give the sample data with XL2BB so that we can easily see what columns/rows the data and results are in and can also easily copy for testing.

 

[Caly]

Hi thank you. Will this help I apologize I’m not able to download but below is the data.


Below is the type of item along with the cost
Type. Cost.
Pear. $0.98
Apple. $1.23
Dishware. $18.99
Supplies. $129

I’m writing the formula like this but getting #value! And #n/a

=Index($q$3:$r$7,
Match(b20,$o$3:$o$7,1),
Match(a20,$q$1:$r$1,0),
If(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))))

Col a, row 20 is the Type like Pear
Col Q row 3. - 7 is the floor values for apple
Col p row 3-7 is the floor values for pear
Col b row 20 is the cost values to match to
Col o rows 3-7 is the start cost in the chart for apple or pears
Col q row 1 is the header name for apple
Col r row 1 is the header name for pear


Below are the chart data values



Dishware
col a. Col b. Col c. Col d.
Row. Seq. Start. End. Floor
3. 1. $0.00. $2.00. 10%
4. 2. $2.01. $4. 12%
5. 3. $4.01. $7.50. 15%
6. 4 $7.51. $12. 18%
7. 5. $12.01. $20. 20%
8. 6. $20.01. $50. 22%
9. 7. $50.01. $100. 16%
10. 8. $100.01. $$250. 12%
11. 9. $250.01. $500. 14%
12. 10. $500.01. $800. 8%


Supplies
col a. Col b. Col c. Col d.
Row. Seq. Start. End. Floor
3. 1. $0.00. $2.00. 18%
4. 2. $2.01. $4. 20%
5. 3. $4.01. $7.50. 28%
6. 4 $7.51. $12. 30%
7. 5. $12.01. $20. 20%
8. 6. $20.01. $50. 22%
9. 7. $50.01. $100. 16%
10. 8. $100.01. $$250. 12%
11. 9. $250.01. $500. 14%
12. 10. $500.01. $800. 8%

Row. Col O. Col p. Col q. Col r
2. Start. End. Apple. Pear
3. $0. $19.99. 12%. 15%
4. $20. $49.99. 15%. 10%
5. $50. $99.99. 20%. 45%
6. $100. $249.99. 25%. 40%
7. $250. $500. 44%. 50%

 

[Peter_SSs]

Will this help

Not really - it is too hard to work out what is in what cells and to get it into a sheet to test with. With your small sample, try just copying it directly from your worksheet and pasting in your post and tell us what columns and rows are shown. For example, the following is D1:F3 and the formula in F1 is =2*E1

Mon	5​	10​
Tue	6​	12​
Wed	2​	4​

 

[Caly]

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%