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