Looking for a solution to my mock up business spreadsheet, that may or may not exist.

[Snapax]

Hey guys,
I've created a little fake business spreadsheet to play around with excel with but I've run into a roadblock that I'm not really sure how I can fix. In one sheet I have memberships that show a start date and an end date of said customers and whether or not they are displayed as 'Active'.

t.png

On another sheet there are the job listings.
tt.png

Currently, the 'Member?' formula is =IFNA(IF(MATCH(D5750,Memberships!D:D,0),true),false) So that if the customer appears in the membership tab once typed in the Member? tab will display as True and they will be charged discounted prices.
However, this does not count whether their membership is active or not.

What im looking is for a way to match the customer to the membership sheet and figure out if their membership is active before it displays TRUE.
Not sure if there is a solution to this or if I could find a better way to change this. Any help would be appreciated, thanks guys.

[bebo021999]

Without any clues, just guess:

=ISNUMBER(MATCH(D5750,Memberships!D:D,0))

[Snapax]

I'm looking for something that can still use =IFNA(IF(MATCH(D5750,Memberships!D:D,0),true),false) but somehow also match it to that customers Active status or not.

[JohnTopley]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[bebo021999]

Try countifs.
Assum column H:H contains "Active"

=COUNTIFS(Memberships!D:D,D5750,Memberships!H:H,"Active")>0

[Snapax]

I've uploaded a sample workbook that I created to show a rough example. What I want is the Member? column to somehow check if the customer is in the membership sheet and then to check if their membership is Active or Inactive. As opposed to the current formula of =IFNA(IF(MATCH(Jobs!C2,Memberships!C:C,0),TRUE),FALSE) which does half the job.
Hopefully I've done it right now, thanks.

[bebo021999]

Try:
=COUNTIFS($C$21:$C$32,C2,$G$21:$G$32,"Active")>0

You may need to check if they are members AND date falls within both start and end dates:
=COUNTIFS($C$21:$C$32,C2,$G$21:$G$32,"Active",$A$21:$A$32,"<="&A2,$F$21:$F$32,">="&A2)>0

[Snapax]

So I don't entirely understand how it works but It seems to be working for the actual version too. Thanks a lot man.