Vlookup with a Y answer

[EdieB]

I have been charged with coming up with a spreadsheet that will show if someone attended a function each week. I have set up one tab (Weekly Attendees) to include the names and the dates attended. I would like another tab (Attendance) to have the names in Col. A with the Dates on line 1 for 52 columns. I would like the result to be Y when the person attends based on the date from the Weekly Attendee Tab. I know how to use Vlookup and Sumif, but I can't figure out how to get a Y (or blank if they didn't attend) if they attend on a specific date.

Can someone help?

 

[Joe4]

Assuming that you know to create a working VLOOKUP, and if they do not attend, the VLOOKUP returns the #N/A error (because it cannot find them), try a structure like this:

=IF(ISERROR(VLOOKUP(...)),"","Y")

Alternatively, you could use COUNTIF, i.e.

=IF(COUNTIF(...)>0,"Y","")

 

[AlanaT]

try surrounding your vlookup with and if formula, i.e. =if(vlookup(A5,sheet1!$A$1:$B$5,2,0)=sheet2!$B$2,"Y","")
you would have toset up sheet2 wih all the attendee name down colmn A and all the functio dates across row 1.

 

[EdieB]

Thanks guys for the help. Problem solved.

 

[EdieB]

Thanks guys for the help. Problem solved.

My spreadsheet had names in column A with dates for 52 weeks going across the columns. I need the formula to look at the name in A and the Date in each of the 52 columns to return the Y or blank.

I have tried editing the formula to do this but I am getting a message of too few arguments.

=if(countifs(Sheet1,a2:a200,a2,sheet1,b2:b200,b1)>0."Y","")

What am I doing wrong? Please and thank you.

 

[Joe4]

By:

Sheet1,a2:a200

do you mean range A2:A200 on Sheet1?

If so, it needs to be written like this:

Sheet1!A2:A200

and you will need to update the other range reference as well.

Commas separate arguments in functions.

 

[EdieB]

This is my formula: =IF(COUNTIFS(Attendees!$A$2:$A$200,A2,Attendees!B2:B200,B1)>0,"Y","")

 

[Joe4]

This is my formula: =IF(COUNTIFS(Attendees!$A$2:$A$200,A2,Attendees!B2:B200,B1)>0,"Y","")

So, it is not working as expected?

If not, we are going to need more details.
What sheet are the criteria "A2" and "B1" on?
What is in those cells?
What cell (on which sheet) is this formula being placed in?
Is it being copied? If so, down or across?
It would also be beneficial to see a small snippet of the data on the Attendees sheet.

 

[EdieB]

Attendee Sheet (where the data is)
Col A Col B
Peter Rabbit 2/1/19
Pied Piper 2/1/19
Peter Rabbit 3/1/19
Pied Piper 3/1/19

Attendance by Member (where the formula will be)
Col. A Col B. Col C.
Name 2/1/19 3/1/19
Row 1 Peter Rabbit
Row 2 Pied Piper

I am wanting the formula to look and see if these two attended on each of the dates in the columns across the page for 52 weeks. If the answer is Yes, I would like Y to appear under the date. If they didn't attend, I would like a blank under the date.

 

[Joe4]

You were close. The issue was understanding fixed, relative, and mixed range references. You need to "lock" down certain row/column references as you copy the formula, or else it will not change properly.
You do that by adding $ in the correct places (in front of the row/column reference you need to lock).

So the formula should look like (the new ones I added are highlighted in red):

=IF(COUNTIFS(Attendees!$A$2:$A$200,[COLOR=#ff0000]$[/COLOR]A2,Attendees![COLOR=#ff0000]$[/COLOR]B[COLOR=#ff0000]$[/COLOR]2:[COLOR=#ff0000]$[/COLOR]B[COLOR=#ff0000]$[/COLOR]200,B[COLOR=#ff0000]$[/COLOR]1)>0,"Y","")

To see an explanation, see this: http://excel.officetuts.net/en/training/relative-absolute-and-mixed-cell-references