# Index match based on criteria in multiple sections

#### Caly

##### Board Regular
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”

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### BSALV

##### Banned user
Map1
ABCDEFGHIJ
1seqcost startcost endapplepearcost startpear
21022%3%2,015%
322,0154%5%
4
Cell Formulas
RangeFormula
I2I2=INDEX(\$A\$2:\$E\$3,MATCH(H2,\$B\$2:\$B\$3,0),MATCH(I1,\$A\$1:\$E\$1,0))

#### Caly

##### Board Regular
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

##### Banned user
Caly
caly.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
1dishwareSuppliesHardwareSoftware
2seqcost startcost endapplepearseqcost startcost endapplepearseqcost startcost endapplepearseqcost startcost endapplepear
31020.020.031020.020.031020.020.031020.020.03
422.0150.040.0522.0150.040.0522.0150.040.052550.040.05
5
6whatsoftware18
7fruitapple
8cost Start5
9
10result0.04
11
Cell Formulas
RangeFormula
E6E6=MATCH(D6,1:1,0)-1
D10D10=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

##### Board Regular
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))))

Replies
14
Views
483
Replies
1
Views
780
Replies
3
Views
95
Replies
7
Views
111
Replies
1
Views
180

1,181,673
Messages
5,931,350
Members
436,787
Latest member
ogharipour

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back