Index match based on criteria in multiple sections

Caly

Board Regular
Joined
Jul 19, 2015
Messages
126
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
Map1
ABCDEFGHIJ
1seqcost startcost endapplepearcost startpear
21022%3%2,015%
322,0154%5%
4
Blad2
Cell Formulas
RangeFormula
I2I2=INDEX($A$2:$E$3,MATCH(H2,$B$2:$B$3,0),MATCH(I1,$A$1:$E$1,0))
 
Solution

Caly

Board Regular
Joined
Jul 19, 2015
Messages
126
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
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
Blad1
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
Joined
Jul 19, 2015
Messages
126
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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

ABCDEFGHIJKLMNOPQRST
1DishwareSuppliesApplePearTypeCostResulting floor based on the type and its costWritten formula
2SeqStartEndFloorSeqStartEndFloorStartEndFloorFloorPear0.984%=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))))
310.00210%10.00222%0.0019.992%4%Supplies432%
422.01412%22.01432%2049.993%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))))
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top