| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find the second match using the match function | Excel Worksheet Functions | |||
| how can we get unique values in match function for same match key. | Excel Worksheet Functions | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| Match as well as does not match array function | Excel Discussion (Misc queries) | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Linear Mode
