OR function with MATCH ?
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.
--
Biff
Microsoft Excel MVP
"Bernard Liengme" wrote in message
...
Very cleaver!
I would not of thought of using SIGN like this.
Cheers
--
Bernard
"T. Valko" wrote in message
...
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))
--
Biff
Microsoft Excel MVP
"ExcelTomcat787" wrote in
message ...
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.
|