ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   OR function with MATCH ? (https://www.excelbanter.com/excel-discussion-misc-queries/241246-function-match.html)

ExcelTomcat787
OR function with MATCH ?
 
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
OR function with MATCH ?
 
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.



Bernard Liengme[_3_]
OR function with MATCH ?
 
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.

Domenic[_2_]
OR function with MATCH ?
 
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.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
ExcelTomcat787 wrote:

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.

ExcelTomcat787
OR function with MATCH ?
 
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!

"ExcelTomcat787" wrote:

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
OR function with MATCH ?
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"ExcelTomcat787" wrote in message
...
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!

"ExcelTomcat787" wrote:

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
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.

ExcelTomcat787
OR function with MATCH ?
 
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" wrote:

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.

ExcelTomcat787
OR function with MATCH ?
 
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!




"ExcelTomcat787" wrote:

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
OR function with MATCH ?
 
Try something like this (array entered):

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

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

--
Biff
Microsoft Excel MVP


"ExcelTomcat787" wrote in message
...
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" wrote:

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
OR function with MATCH ?
 
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.

--
Biff
Microsoft Excel MVP


"ExcelTomcat787" wrote in message
...
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!




"ExcelTomcat787" wrote:

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.



Domenic[_2_]
OR function with MATCH ?
 
In article ,
ExcelTomcat787 wrote:

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!




"ExcelTomcat787" wrote:

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.


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.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

T. Valko
OR function with MATCH ?
 
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.

--
Biff
Microsoft Excel MVP


"Domenic" wrote in message
...
In article ,
ExcelTomcat787 wrote:

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!




"ExcelTomcat787" wrote:

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.


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.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions



T. Valko
OR function with MATCH ?
 
Ok, this *seems* to work. Array entered:

=INDEX(J2:J16,MATCH(1,(SUBTOTAL(3,OFFSET(B2:B16,RO W(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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Domenic" wrote in message
...
In article ,
ExcelTomcat787 wrote:

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!




"ExcelTomcat787" wrote:

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.


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.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

Domenic[_2_]
OR function with MATCH ?
 
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")&"cu rrent"),0))

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

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Domenic" wrote in message
...
In article ,
ExcelTomcat787 wrote:

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!




"ExcelTomcat787" wrote:

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.


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.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

ExcelTomcat787
OR function with MATCH ?
 
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.



"T. Valko" wrote:

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

=INDEX(J2:J16,MATCH(1,(SUBTOTAL(3,OFFSET(B2:B16,RO W(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.

--
Biff
Microsoft Excel MVP


"Domenic" wrote:

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")&"cu rrent"),0))

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

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Domenic" wrote in message
...
In article ,
ExcelTomcat787 wrote:

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!




"ExcelTomcat787" wrote:

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.


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.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

T. Valko
OR function with MATCH ?
 
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.

--
Biff
Microsoft Excel MVP


"ExcelTomcat787" wrote in message
...
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.



"T. Valko" wrote:

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

=INDEX(J2:J16,MATCH(1,(SUBTOTAL(3,OFFSET(B2:B16,RO W(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.

--
Biff
Microsoft Excel MVP


"Domenic" wrote:

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")&"cu rrent"),0))

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

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Domenic" wrote in message
...
In article ,
ExcelTomcat787 wrote:

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!




"ExcelTomcat787" wrote:

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.


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.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions


All times are GMT +1. The time now is 09:31 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com