IF Match formula - multiple sheets in lookup_array

[azonicds2]

Hi all

I have a problem which i just cant seem to get my head around.

I have 13 sheets (12 months and a summary sheet) and a list of names down the left hand side for users.

I'd like to lookup in any of the columns on alternate monthly sheets (e.g. January, March, May etc), if the term "NC" exists. and if it does on the summary sheet in the column where i am placing my formula id like to display "yes" or something like.

So my formula so far is:

=IF(ISNA(MATCH("NC",'January 2016'!H2,0)),"","Yes")

which works great for looking up the H2 cell on the January sheet but how can i make it match against the H2 cell on every other monthly sheet? I've tried so many different ways im just so confused now. ha. I understand that to make it do every other sheet that i'll probably end up with a very cumbersome formula.

Any help would be appreciated, maybe i shouldn't even be using Match at all?

Thanks very much

Danny

 

[FDibbins]

This looks like a cross-post to a thread in Excel Forum?
(unable to log into that site right now, so cannot provide the link)

 

[BobUmlas]

You can use a UDF (user-defined function):

Function EveryOtherMonth() As String
For i = 1 To 12 Step 2
If Sheets(Format(i & "/2000", "mmmm")).Range("H2").Value = "NC" Then
Debug.Print Sheets(i).Name
EveryOtherMonth = "Yes"
Exit Function
End If
Next
EveryOtherMonth = ""
End Function

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=IF(ISNUMBER(MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetList&"'!H2"),"NC")>0,0)),"Yes","No")<strike></strike>

where SheetList is the name of the range which houses the names of the relevant sheets, i.e., January, etc.

 

[azonicds2]

Control+shift+enter, not just enter:

=IF(ISNUMBER(MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetList&"'!H2"),"NC")>0,0)),"Yes","No")<strike></strike>

where SheetList is the name of the range which houses the names of the relevant sheets, i.e., January, etc.

Hey, thanks for this. i have found a method that is working for me now, but this is always useful to know another way.

Thanks again

 

[FDibbins]

Just for the record, what worked for you?