IF Match formula - multiple sheets in lookup_array

azonicds2

New Member
Joined
Mar 17, 2016
Messages
2
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

Well-known Member
Joined
Mar 14, 2002
Messages
1,181
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Mar 17, 2016
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,531
Messages
5,602,209
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top