I have multiple criteria's & one may be blanks, how to skip blank criteria in formula?

[Tony O]

I have multiple criteria's & one may be blanks, how to skip blank criteria in formula?
Below is formula that works with no blank cell criteria's
=INDEX($G$2:$G$50,MATCH(1,INDEX((J2=$C$2:$C$50)*(J16=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$E$2:$E$50)*(J19=$F$2:$F$50),0,1),0))

If cell J2 is blank how to correct above formula see below?
(J2=$C$2:$C$50

& can it work on multipile blank cirteria's?

Regards

Tony O

 

[awoohaw]

Maybe you can try this:
((J2=$C$2:$C$50)+(""=$C$2:$C$50)) for that part of the formula.

 

[Peter_SSs]

Welcome to the MrExcel board!

Are you saying that if J2 is blank then ignore the test for J2 in column C?
If so, try this modification.
=INDEX($G$2:$G$50,MATCH(1,INDEX(IF(J2="",1,J2=$C$2:$C$50)*(J16=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$E$2:$E$50)*(J19=$F$2:$F$50),0,1),0))

If that is what you want then you can make similar changes to the other sections.

 

[Tony O]

Hi All The below formula only works with criteria, doesn't work with blank criteria. But had to add extra brackets ( after (1,INDEX(((( but no closing brackets? =INDEX($G$2:$G$50,MATCH(1,INDEX((((K2=$C$2:$C$50)+(""=$C$2:$C$50))*(K14=$D$2:$D$50)+(""=$D$2:$D$50))*(K17=$E$2:$E$50)*(K18=$F$2:$F$50),0,1),0)) The below formula works in J2 with or without blank criteria, but haven't worked out how to place in other criteria's? =INDEX($G$2:$G$50,MATCH(1,INDEX(IF(J2="",1,J2=$C$2:$C$50)*(J14=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$F$2:$F$50),0,1),0)) The other question, the formula below is the same as above but only work with criteria, What is different between both as i'm stump? =INDEX($G$2:$G$50,MATCH(1,INDEX(IF(J2="",1,J2=$C$2:$C$50)*(J14=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$F$2:$F$50),0,1),0)) Regards Tony O

 

[Peter_SSs]

Please don't type your replies in a table like you have above.

The below formula works in J2 with or without blank criteria, but haven't worked out how to place in other criteria's?

=INDEX($G$2:$G$50,MATCH(1,INDEX(IF(J2="",1,J2=$C$2:$C$50)*(J14=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$F$2:$F$50),0,1),0))

Your formula was
=INDEX($G$2:$G$50,MATCH(1,INDEX((J2=$C$2:$C$50)*(J16=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$E$2:$E$50)*(J19=$F$2:$F$50),0,1),0))

I replaced the blue part above with the blue part below and said to do the same thing with the other sections.
=INDEX($G$2:$G$50,MATCH(1,INDEX(IF(J2="",1,J2=$C$2:$C$50)*(J16=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$E$2:$E$50)*(J19=$F$2:$F$50),0,1),0))

So here is the next section changed in the same way, changing the red part above into the red part below.
=INDEX($G$2:$G$50,MATCH(1,INDEX(IF(J2="",1,J2=$C$2:$C$50)*IF(J16="",1,J16=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$E$2:$E$50)*(J19=$F$2:$F$50),0,1),0))

Now you need to change the other three sections in a similar way.

 

[awoohaw]

Hi All The below formula only works with criteria, doesn't work with blank criteria. But had to add extra brackets ( after (1,INDEX(((( but no closing brackets? =INDEX($G$2:$G$50,MATCH(1,INDEX((((K2=$C$2:$C$50)+(""=$C$2:$C$50))*(K14=$D$2:$D$50)+(""=$D$2:$D$50))*(K17=$E$2:$E$50)*(K18=$F$2:$F$50),0,1),0)) The below formula works in J2 with or without blank criteria, but haven't worked out how to place in other criteria's? =INDEX($G$2:$G$50,MATCH(1,INDEX(IF(J2="",1,J2=$C$2:$C$50)*(J14=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$F$2:$F$50),0,1),0)) The other question, the formula below is the same as above but only work with criteria, What is different between both as i'm stump? =INDEX($G$2:$G$50,MATCH(1,INDEX(IF(J2="",1,J2=$C$2:$C$50)*(J14=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$F$2:$F$50),0,1),0)) Regards Tony O

Maybe you need to post a mini example of your data. Please use the xl2bb link below. If you cannot do that, then please post as a table.
(Please do not just post an image). Label the table rows and columns so they match your formulas (if you use the table method of posting the data.).

 

[Tony O]

Please don't type your replies in a table like you have above.

Your formula was
=INDEX($G$2:$G$50,MATCH(1,INDEX((J2=$C$2:$C$50)*(J16=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$E$2:$E$50)*(J19=$F$2:$F$50),0,1),0))

I replaced the blue part above with the blue part below and said to do the same thing with the other sections.
=INDEX($G$2:$G$50,MATCH(1,INDEX(IF(J2="",1,J2=$C$2:$C$50)*(J16=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$E$2:$E$50)*(J19=$F$2:$F$50),0,1),0))

So here is the next section changed in the same way, changing the red part above into the red part below.
=INDEX($G$2:$G$50,MATCH(1,INDEX(IF(J2="",1,J2=$C$2:$C$50)*IF(J16="",1,J16=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$E$2:$E$50)*(J19=$F$2:$F$50),0,1),0))

Now you need to change the other three sections in a similar way.

Hi All

The formula below only reads first INDEX G2, If change $G$2 to $G$3 it reads G3 only

=INDEX($G$2:$G$50,MATCH(1,INDEX(IF(J2="",1,J2=$C$2:$C$50)*IF(J14="",1,J14=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$F$2:$F$50),0,1),0))

I have check formula for reference & looks up index & first match in J2 but the rest is blank.

The formula below only reads blank criteria's, but if both criteria are full won't read & gives N/A & same if one criteria is blank.

=INDEX($G$2:$G$50,MATCH(1,INDEX(IF(J2="",1,J2=$C$2:$C$50)*IF(J14="",1,J14=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$F$2:$F$50),0,1),0))

Regards

Tony O

 

[awoohaw]

Hi All

The formula below only reads first INDEX G2, If change $G$2 to $G$3 it reads G3 only

=INDEX($G$2:$G$50,MATCH(1,INDEX(IF(J2="",1,J2=$C$2:$C$50)*IF(J14="",1,J14=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$F$2:$F$50),0,1),0))

I have check formula for reference & looks up index & first match in J2 but the rest is blank.

The formula below only reads blank criteria's, but if both criteria are full won't read & gives N/A & same if one criteria is blank.

=INDEX($G$2:$G$50,MATCH(1,INDEX(IF(J2="",1,J2=$C$2:$C$50)*IF(J14="",1,J14=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$F$2:$F$50),0,1),0))

Regards

Tony O

Tony, please post a sample of your data.

 

[Peter_SSs]

The first thing that I notice is that the formula in post 7 is significantly different to the formulas in earlier posts.

• Testing column D in post 7 uses J14. In the earlier posts testing column D used J16
• Testing column E in post 7 uses J17. In the earlier posts column E was tested against both J17 and J18 as per post 1:
  
  
  

Can you clarify exactly what should be happening in relation to the two points above.

I also note from your formula in post 7 that you have not updated the remaining sections of the formula in the manner outlined in post 5

 

[Tony O]

The first thing that I notice is that the formula in post 7 is significantly different to the formulas in earlier posts.

• Testing column D in post 7 uses J14. In the earlier posts testing column D used J16
• Testing column E in post 7 uses J17. In the earlier posts column E was tested against both J17 and J18 as per post 1:
  
  View attachment 100725

Can you clarify exactly what should be happening in relation to the two points above.

I also note from your formula in post 7 that you have not updated the remaining sections of the formula in the manner outlined in post 5

Hi Peter

I have trim & clean all the data in column's C,D,E,F & G with the formula below.

=TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " ")))

The below formula for 2nd match when push enter says There is a problem with this formula?

INDEX($G$2:$G$50,MATCH(1,INDEX(IF(J2="",1,J2=$C$2:$C$50)*(J14="",1,J14=$D$2:$D$50)*(J17=$E$2:$E$50)*(J18=$F$2:$F$50),0,1),0))

The formula moved down the excel sheet, as when I try a formula that isn't working I move 2 cells down the column & try a new formula.
When a formula works in match, I mark it in green as in J2. It works with filled cell or blank cell.
Then move down 2 cell of column & start new formula with 2nd match J16, where by this only works with both blank cells only?
Haven't being able to get past the 2nd Match & I have left out J19 altogether

Regards

Tony O