Trouble with Vlookup formula for multiple criteria on different sheets.

[robgoldstein]

HI All
I am having a problem with my vlookup formula.
I am trying to find the the info in column X in my "data" sheet for rows where the Date (cell A1) and email (Column B) from "team" sheet match the date in my "Data" sheet in column C and the email in column B on the DATA sheet

Here is formula I am using, but I am getting an error.
=VLOOKUP($b3&"|"$a$1,choose({1,2},DATA!B:B&"|"&DATA!C:C,DATA!X:X),2,0)}

Any help is greatly appreciated.

tp covid screener (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Timestamp	Email address	Event Date	Parent/Guardian filling out this form, please enter your full name below:	Please enter your participants name	Do you have a fever?	Do you have a cough?	Do you have difficulty breathing?	Do you have a sore throat or trouble swallowing?	Do you have a runny nose or red eyes?	Have you lost taste or smell?	Have you experience sore muscles or feeling unwell?	Do you have nausea, vomiting or diarrhea?	Have you been in close contact with someone who has been sick or has confirmed case of Covid-19 in the past 14 days? *	Have you returned from travel outside of Canada in the last 14 days?	TORONTO.CA/COVID19 Do you have any of the following: If you answered YES to any of these questions, go home & self-isolate right away. Call Telehealth or your health care provider, to find out if you need a test. *	I verify that I have not answered Yes to any of the above questions
2	7-4-2021 11:06:01	rob@gmail.com	2021-07-04	rob goldstein	alex goldstein	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions
3	7-4-2021 11:28:17	rob@gmail.com	2021-07-04	test dad	test kid	No	No	Yes	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I have answered YES, and will not attend baseball activities today
4	7-4-2021 13:55:51	Ginney@hogwarts.com	2021-07-04	rob G	dumbass	No	No	No	No	No	No	Yes	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions
5	7-4-2021 15:49:07	rob@gmail.com	2021-07-04	rob g	Alex G	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions
6	7-4-2021 22:59:35	ralf@machio.com	2021-07-05	Ralf Machio SR	Ralf Machio Jr.	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I have answered YES, and will not attend baseball activities today
7	7-4-2021 23:06:26	RalfMachio@gmail.com	2021-07-04	RAlf SR	Ralf Jr	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions
8	7-4-2021 23:22:24	HarryPotter@hogwarts.com	2021-07-04	Dumble Door	Harry P	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions
Form responses 1

tp covid screener (1).xlsx
	A	B	C	D
1	2021-07-08			Cleared email 1
2	Player	Email	Email 2
3	Alex	rob@gmail.com	Jen@gmail.com	#ERROR!
4	Dave	rob@gmail.com	Jodi@gmail.com
5	Jen	rob@gmail.com	dave@gmail.com
6	Steve	rob@gmail.com
7	Rick	ralf@machio.com	steve@gmail.com
8	Ralf Jr	RalfMachio@gmail.com	MsExel@gmail.com
9	Ron W	HarryPotter@hogwarts.com	Ginney@hogwarts.com
Team 1

Cell Formulas
Range	Formula
A1	A1	=TODAY()

 

[Toadstool]

Hi Robgoldstein,

You didn't show column X so I've dummied up some values. I've also changed some Event Dates so some will match. You don't say what should happen if no match is found so I've returned "No match".

Here your data sheet

 

[Toadstool]

Whoops post too early

Here's your data sheet

RobGoldstein2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R						X
1	Timestamp	Email address	Event Date	Parent/Guardian filling out this form, please enter your full name below:	Please enter your participants name	Do you have a fever?	Do you have a cough?	Do you have difficulty breathing?	Do you have a sore throat or trouble swallowing?	Do you have a runny nose or red eyes?	Have you lost taste or smell?	Have you experience sore muscles or feeling unwell?	Do you have nausea, vomiting or diarrhea?	Have you been in close contact with someone who has been sick or has confirmed case of Covid-19 in the past 14 days? *	Have you returned from travel outside of Canada in the last 14 days?	TORONTO.CA/COVID19 Do you have any of the following: If you answered YES to any of these questions, go home & self-isolate right away. Call Telehealth or your health care provider, to find out if you need a test. *	I verify that I have not answered Yes to any of the above questions							X
2	7-4-2021 11:06:01	rob@gmail.com	2021-07-04	rob goldstein	alex goldstein	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions							Mole
3	7-4-2021 11:28:17	rob@gmail.com	2021-07-08	test dad	test kid	No	No	Yes	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I have answered YES, and will not attend baseball activities today							Dog
4	7-4-2021 13:55:51	Ginney@hogwarts.com	2021-07-08	rob G	dumbass	No	No	No	No	No	No	Yes	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions							Cat
5	7-4-2021 15:49:07	rob@gmail.com	2021-07-04	rob g	Alex G	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions							Goat
6	7-4-2021 22:59:35	ralf@machio.com	2021-07-08	Ralf Machio SR	Ralf Machio Jr.	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I have answered YES, and will not attend baseball activities today							Sheep
7	7-4-2021 23:06:26	RalfMachio@gmail.com	2021-07-04	RAlf SR	Ralf Jr	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions							Horse
8	7-4-2021 23:22:24	HarryPotter@hogwarts.com	2021-07-08	Dumble Door	Harry P	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions							Cow
Form Responses 1

Here's the Team sheet

RobGoldstein2.xlsx
	A	B	C	D
1	7/8/2021			Cleared email 1
2	Player	Email	Email 2
3	Alex	rob@gmail.com	Jen@gmail.com	Dog
4	Dave	rob@gmail.com	Jodi@gmail.com	Dog
5	Jen	rob@gmail.com	dave@gmail.com	Dog
6	Steve	rob@gmail.com		Dog
7	Rick	ralf@machio.com	steve@gmail.com	Sheep
8	Ralf Jr	RalfMachio@gmail.com	MsExel@gmail.com	No match
9	Ron W	HarryPotter@hogwarts.com	Ginney@hogwarts.com	Cow
Team 1

Cell Formulas
Range	Formula
A1	A1	=TODAY()
D3:D9	D3	=IFERROR(INDEX('Form Responses 1'!$X$2:$X$9999,MATCH(1,INDEX(('Form Responses 1'!$C$2:$C$9999=$A$1)*('Form Responses 1'!$B$2:$B$9999=B3),0,1),0)),"No match")

 

[robgoldstein]

Whoops post too early

Here's your data sheet

RobGoldstein2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R						X
1	Timestamp	Email address	Event Date	Parent/Guardian filling out this form, please enter your full name below:	Please enter your participants name	Do you have a fever?	Do you have a cough?	Do you have difficulty breathing?	Do you have a sore throat or trouble swallowing?	Do you have a runny nose or red eyes?	Have you lost taste or smell?	Have you experience sore muscles or feeling unwell?	Do you have nausea, vomiting or diarrhea?	Have you been in close contact with someone who has been sick or has confirmed case of Covid-19 in the past 14 days? *	Have you returned from travel outside of Canada in the last 14 days?	TORONTO.CA/COVID19 Do you have any of the following: If you answered YES to any of these questions, go home & self-isolate right away. Call Telehealth or your health care provider, to find out if you need a test. *	I verify that I have not answered Yes to any of the above questions							X
2	7-4-2021 11:06:01	rob@gmail.com	2021-07-04	rob goldstein	alex goldstein	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions							Mole
3	7-4-2021 11:28:17	rob@gmail.com	2021-07-08	test dad	test kid	No	No	Yes	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I have answered YES, and will not attend baseball activities today							Dog
4	7-4-2021 13:55:51	Ginney@hogwarts.com	2021-07-08	rob G	dumbass	No	No	No	No	No	No	Yes	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions							Cat
5	7-4-2021 15:49:07	rob@gmail.com	2021-07-04	rob g	Alex G	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions							Goat
6	7-4-2021 22:59:35	ralf@machio.com	2021-07-08	Ralf Machio SR	Ralf Machio Jr.	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I have answered YES, and will not attend baseball activities today							Sheep
7	7-4-2021 23:06:26	RalfMachio@gmail.com	2021-07-04	RAlf SR	Ralf Jr	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions							Horse
8	7-4-2021 23:22:24	HarryPotter@hogwarts.com	2021-07-08	Dumble Door	Harry P	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions							Cow
Form Responses 1

Here's the Team sheet

RobGoldstein2.xlsx
	A	B	C	D
1	7/8/2021			Cleared email 1
2	Player	Email	Email 2
3	Alex	rob@gmail.com	Jen@gmail.com	Dog
4	Dave	rob@gmail.com	Jodi@gmail.com	Dog
5	Jen	rob@gmail.com	dave@gmail.com	Dog
6	Steve	rob@gmail.com		Dog
7	Rick	ralf@machio.com	steve@gmail.com	Sheep
8	Ralf Jr	RalfMachio@gmail.com	MsExel@gmail.com	No match
9	Ron W	HarryPotter@hogwarts.com	Ginney@hogwarts.com	Cow
Team 1

Cell Formulas
Range	Formula
A1	A1	=TODAY()
D3:D9	D3	=IFERROR(INDEX('Form Responses 1'!$X$2:$X$9999,MATCH(1,INDEX(('Form Responses 1'!$C$2:$C$9999=$A$1)*('Form Responses 1'!$B$2:$B$9999=B3),0,1),0)),"No match")

thanks so much. The date is the event date in column C on the data page. If there are no matches I would like it to say "not eligible"
.

 

[robgoldstein]

Whoops post too early

Here's your data sheet

RobGoldstein2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R						X
1	Timestamp	Email address	Event Date	Parent/Guardian filling out this form, please enter your full name below:	Please enter your participants name	Do you have a fever?	Do you have a cough?	Do you have difficulty breathing?	Do you have a sore throat or trouble swallowing?	Do you have a runny nose or red eyes?	Have you lost taste or smell?	Have you experience sore muscles or feeling unwell?	Do you have nausea, vomiting or diarrhea?	Have you been in close contact with someone who has been sick or has confirmed case of Covid-19 in the past 14 days? *	Have you returned from travel outside of Canada in the last 14 days?	TORONTO.CA/COVID19 Do you have any of the following: If you answered YES to any of these questions, go home & self-isolate right away. Call Telehealth or your health care provider, to find out if you need a test. *	I verify that I have not answered Yes to any of the above questions							X
2	7-4-2021 11:06:01	rob@gmail.com	2021-07-04	rob goldstein	alex goldstein	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions							Mole
3	7-4-2021 11:28:17	rob@gmail.com	2021-07-08	test dad	test kid	No	No	Yes	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I have answered YES, and will not attend baseball activities today							Dog
4	7-4-2021 13:55:51	Ginney@hogwarts.com	2021-07-08	rob G	dumbass	No	No	No	No	No	No	Yes	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions							Cat
5	7-4-2021 15:49:07	rob@gmail.com	2021-07-04	rob g	Alex G	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions							Goat
6	7-4-2021 22:59:35	ralf@machio.com	2021-07-08	Ralf Machio SR	Ralf Machio Jr.	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I have answered YES, and will not attend baseball activities today							Sheep
7	7-4-2021 23:06:26	RalfMachio@gmail.com	2021-07-04	RAlf SR	Ralf Jr	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions							Horse
8	7-4-2021 23:22:24	HarryPotter@hogwarts.com	2021-07-08	Dumble Door	Harry P	No	No	No	No	No	No	No	No	No	No	I have read the city of Toronto Notice	I acknowledge I have not answered YES to any of the above questions							Cow
Form Responses 1

Here's the Team sheet

RobGoldstein2.xlsx
	A	B	C	D
1	7/8/2021			Cleared email 1
2	Player	Email	Email 2
3	Alex	rob@gmail.com	Jen@gmail.com	Dog
4	Dave	rob@gmail.com	Jodi@gmail.com	Dog
5	Jen	rob@gmail.com	dave@gmail.com	Dog
6	Steve	rob@gmail.com		Dog
7	Rick	ralf@machio.com	steve@gmail.com	Sheep
8	Ralf Jr	RalfMachio@gmail.com	MsExel@gmail.com	No match
9	Ron W	HarryPotter@hogwarts.com	Ginney@hogwarts.com	Cow
Team 1

Cell Formulas
Range	Formula
A1	A1	=TODAY()
D3:D9	D3	=IFERROR(INDEX('Form Responses 1'!$X$2:$X$9999,MATCH(1,INDEX(('Form Responses 1'!$C$2:$C$9999=$A$1)*('Form Responses 1'!$B$2:$B$9999=B3),0,1),0)),"No match")

@Toadstool Thanks so much, but this is not 100% what I am looking for.
If there is a row that has the email and the right date, I need it to pull the info from column X. If there isn't a row with both of those matching I need it to be blank. Sorry for the confusion.

 

[Toadstool]

@Toadstool Thanks so much, but this is not 100% what I am looking for.
If there is a row that has the email and the right date, I need it to pull the info from column X. If there isn't a row with both of those matching I need it to be blank. Sorry for the confusion.

Yes, it's checking for the same row that the Event date in column C matches the Team sheet date in cell A1 and that the Email address in column B of Team Responses1 matches the Team sheet email address in column B.

If it doesn't find a match for either then you can just change the IFERROR response from "No match" to null, like this:

=IFERROR(INDEX('Form Responses 1'!$X$2:$X$9999,MATCH(1,INDEX(('Form Responses 1'!$C$2:$C$9999=$A$1)*('Form Responses 1'!$B$2:$B$9999=B3),0,1),0)),"")

NOTE: If there are multiple matches then it will only return the Remarks in column X from the first match.