OR function with MATCH ?

[ExcelTomcat787]

How would I use MATCH with the ability to set multiple variables for the match?

My formula is:

=INDEX('Due Now'!$A$2:$K$662,MATCH($B45&"Alpha"&"current",'Due
Now'!$B$2:$B$600&'Due Now'!$E$2:$E$600&'Due Now'!$I$2:$I$600,0),10)

My formula will lookup "B45AlphaCurrent". But I want the Alpha part to be
either "Alpha" OR "Bravo" in this formula. Any thoughts?

Thanks in advance.

 

[T. Valko]

Try it like this (add your sheet name!):

=INDEX($J$2:$J$600,MATCH(1,($B$2:$B$600=$B45)*SIGN(($E$2:$E$600="Alpha")+($E$2:$E$600="Bravo"))*($I$2:$I$600="current"),0))

 

[Bernard Liengme]

Very cleaver!
I would not of thought of using SIGN like this.
Cheers

 

[Domenic]

Here's another way...

=INDEX('Due Now'!$J$2:$J$600,MATCH(1,IF(ISNUMBER(MATCH('Due
Now'!$B$2:$B$600&'Due Now'!$E$2:$E$600&'Due
Now'!$I$2:$I$600,$B45&{"Alpha","Bravo"}&"current",0)),1),0))

....confirmed with CONTROL+SHIFT+ENTER.

 

[ExcelTomcat787]

Brilliant T. Valko and Domenic! Both of your formulas work great. Having
this kind of dynamic for Index|Match makes this a powerful formula combo.
Thanks again for sharing your intelligence!

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[T. Valko]

Now that I think about it...

In this case we don't need to test the "OR" condition to be >0 since it's
based on the same range.

=INDEX($J$2:$J$600,MATCH(1,($B$2:$B$600=$B45)*(($E$2:$E$600="Alpha")+($E$2:$E$600="Bravo"))*($I$2:$I$600="current"),0))

E2:E600 can be either:

Alpha
Bravo
"neither"

But it can't be all 3 of those at the same time. So:

(($E$2:$E$600="Alpha")+($E$2:$E$600="Bravo"))

Will return the array of 1s and 0s we need.

 

[ExcelTomcat787]

This formula is great, however, can it be expanded to make the conditions
have priority over the other? i.e. Can "Alpha" be prioritized first before
"Bravo"? If lookup happened to find "Bravo" first, then we want it to skip
until it found Alpha, unless however there was no Alpha, so then we want the
secondary priority, Bravo. Here's what I mean:

List
Bravo
Alpha
Bravo
(Bravo happens to be before Alpha)

So with our current formula lookup would be:
"B45BravoCurrent"

But what if we want to exhaust all Alphas BEFORE we start with Bravo? So,
the Alpha lookup needs to have priority. The current formula only looks up
the first event that happens to be Alpha or Bravo...not Alpha THEN Bravo.

Let me know what you think!

 

[ExcelTomcat787]

Also have another question. Can the formula be applied to an array based no
filtered results rather than the entire data set?

So if my reference to worksheet 'Due Now'!$A$2:$K$662 had data that was
filtered with the Data|Filter|Autofilter feature and has custom filters set
on certain columns already, how can the Index|Match formula do lookups by
omitting the unfiltered data and only looking up within the filtered data?

Thanks!

 

[T. Valko]

Try something like this (array entered):

=INDEX(J2:J600,MATCH(1,(B2:B600=B45)*(E2:E600=IF(COUNTIF(E2:E600,"alpha"),"alpha","bravo"))*(I2:I600="current"),0))

Assumes that one or the other, Alpha or Bravo, does in fact exist.

 

[T. Valko]

Wow!

That'll be really complicated when combined with your latest question (give
Alpha priority). I don't have time to play with it right now. I'll try it
later this evening.

 

[Domenic]

Also have another question. Can the formula be applied to an array based no
filtered results rather than the entire data set?

So if my reference to worksheet 'Due Now'!$A$2:$K$662 had data that was
filtered with the Data|Filter|Autofilter feature and has custom filters set
on certain columns already, how can the Index|Match formula do lookups by
omitting the unfiltered data and only looking up within the filtered data?

Thanks!

Try...

=INDEX('Due Now'!$J$2:$J$600,MATCH(1,IF(SUBTOTAL(3,OFFSET('Due
Now'!$B$2:$B$600,ROW('Due Now'!$B$2:$B$600)-ROW('Due
Now'!$B$2),0,1)),IF('Due Now'!$B$2:$B$600&'Due Now'!$E$2:$E$600&'Due
Now'!$I$2:$I$600=$B45&IF(ISNUMBER(MATCH("Alpha",IF(SUBTOTAL(3,OFFSET('Due
Now'!$B$2:$B$600,ROW('Due Now'!$B$2:$B$600)-ROW('Due
Now'!$B$2),0,1)),'Due
Now'!$E$2:$E$600),0)),"Alpha","Bravo")&"current",1)),0))

....confirmed with CONTROL+SHIFT+ENTER.

 

[T. Valko]

Hey Dom!

When I try that in Excel 2002 I get the general formula error message: The
formula you typed contains an error....

Hit OK and it highlights the second SUBTOTAL function. Too many nested
levels!

I haven't been able to come up with something that works. I'm getting stuck
on the "if rng = alpha, alpha, bravo" test.

 

[T. Valko]

Ok, this *seems* to work. Array entered:

=INDEX(J2:J16,MATCH(1,(SUBTOTAL(3,OFFSET(B2:B16,ROW(B2:B16)-ROW(B2),0,1)))*(B2:B16=B45)*(E2:E16=IF(SUMPRODUCT(SUBTOTAL(3,OFFSET(E2:E16,ROW(E2:E16)-ROW(E2),0,1)),--(E2:E16="alpha")),"alpha",IF(SUMPRODUCT(SUBTOTAL(3,OFFSET(E2:E16,ROW(E2:E16)-ROW(E2),0,1)),--(E2:E16="bravo")),"bravo",NA())))*(I2:I16="current"),0))

Add the sheet name and adjust the ranges to suit.

 

[Domenic]

Maybe...

=INDEX('Due Now'!$J$2:$J$600,MATCH(1,SUBTOTAL(3,OFFSET('Due
Now'!$B$2:$B$600,ROW('Due Now'!$B$2:$B$600)-ROW('Due
Now'!$B$2),0,1))*('Due Now'!$B$2:$B$600&'Due Now'!$E$2:$E$600&'Due
Now'!$I$2:$I$600=$B45&IF(ISNUMBER(MATCH(1,SUBTOTAL(3,OFFSET('Due
Now'!$B$2:$B$600,ROW('Due Now'!$B$2:$B$600)-ROW('Due
Now'!$B$2),0,1))*('Due
Now'!$E$2:$E$600="Alpha"),0)),"Alpha","Bravo")&"current"),0))

....confirmed with CONTROL+SHIF+ENTER.

 

[ExcelTomcat787]

Did not work. Seems that the conditional variable gets tripped up with the
sumproduct.

A valiant, however, from both T. Valko and Domenic. Thanks for attempting
this!

Please post if you think of anything else.



:

Ok, this *seems* to work. Array entered:

=INDEX(J2:J16,MATCH(1,(SUBTOTAL(3,OFFSET(B2:B16,ROW(B2:B16)-ROW(B2),0,1)))*(B2:B16=B45)*(E2:E16=IF(SUMPRODUCT(SUBTOTAL(3,OFFSET(E2:E16,ROW(E2:E16)-ROW(E2),0,1)),--(E2:E16="alpha")),"alpha",IF(SUMPRODUCT(SUBTOTAL(3,OFFSET(E2:E16,ROW(E2:E16)-ROW(E2),0,1)),--(E2:E16="bravo")),"bravo",NA())))*(I2:I16="current"),0))

Add the sheet name and adjust the ranges to suit.

 

[T. Valko]

Seems that the conditional variable gets tripped up with the sumproduct.

Can you describe the scenario in which it didn't work?

It worked in all of my tests but of course I (we) don't know what your data
looks like.