Search for 1 of 2 inputs and 3rd

[robgoldstein]

Hey All,
I have a sheet that has 2 columns of email addresses and a cell for today's date. I need to search a sheet named "data" for rows that contain one of those email addresses and today's date. If there is a match I need to pull the info from cell X.
Is there a formula that can do this in a single step?

 

[Joe4]

I think it would be helpful to see what your main sheet and your "data" sheet look like, so we know how everything is structured.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[robgoldstein]

I think it would be helpful to see what your main sheet and your "data" sheet look like, so we know how everything is structured.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Here is the "data" sheet.
The info is coming from a google form. We are using the event date, and email address to return the result in column X which shows if they are cleared to play

tp covid screener.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Timestamp	Email	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		Check box combined answer	Cleared to play on check boxes	Total number of No's	Cleared to play on answers	combined answers	CLEARED TO PLAY
2	#############	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		I have read the city of Toronto NoticeI acknowledge I have not answered YES to any of the above questions	TRUE	10	TRUE	TRUETRUE	TRUE
3	#############	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		I have read the city of Toronto NoticeI have answered YES, and will not attend baseball activities today	FALSE	9	FALSE	FALSEFALSE	FALSE
4	#############	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		I have read the city of Toronto NoticeI acknowledge I have not answered YES to any of the above questions	TRUE	9	FALSE	TRUEFALSE	FALSE
5	#############	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		I have read the city of Toronto NoticeI acknowledge I have not answered YES to any of the above questions	TRUE	10	TRUE	TRUETRUE	TRUE
6	#############	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		I have read the city of Toronto NoticeI have answered YES, and will not attend baseball activities today	FALSE	10	TRUE	FALSETRUE	FALSE
7	#############	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		I have read the city of Toronto NoticeI acknowledge I have not answered YES to any of the above questions	TRUE	10	TRUE	TRUETRUE	TRUE
8	#############	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		I have read the city of Toronto NoticeI acknowledge I have not answered YES to any of the above questions	TRUE	10	TRUE	TRUETRUE	TRUE
9	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		00	FALSE	0	FALSE	FALSEFALSE	FALSE
DATA

Cell Formulas
Range	Formula
A2:Q9	A2	='Form responses 1'!A2
S2:S9	S2	=P2&Q2
T2:T9	T2	=ISNUMBER(SEARCH("I have read the city of Toronto NoticeI acknowledge I have not answered YES to any of the above questions",S2))
U2:U9	U2	=COUNTIFS(F2:O2,"no")
V2:V9	V2	=ISNUMBER(SEARCH("10",U2))
W2:W9	W2	=T2&V2
X2:X9	X2	=ISNUMBER(SEARCH("TRUETRUE",W2))

 

[Joe4]

The info is coming from a google form.

Are you trying to do this in "Google Sheets" or "Excel"?

I think you should be able to use VLOOKUP to get what you want.
See: Excel formula: VLOOKUP from another sheet | Exceljet

If you still have any issues, please post how your data on your other sheets looks.

 

[robgoldstein]

Thanks @Joe4 I am trying to use Vlookup, but having trouble with the 2 criteria.
Here is the formula I am using, =VLOOKUP($b3&"|"$a$1,choose({1,2},DATA!B:B&"|"&DATA!C:C,DATA!X:X),2,0)}

 

[Joe4]

If you still have any issues, please post how your data on your other sheets looks.

I meant post a screen print of the other sheet, like you did for the Data sheet.

 

[robgoldstein]

tp covid screener (1).xlsx
	A	B	C	D
1	2021-07-04	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
B1	B1	=TODAY()

tp covid screener (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Timestamp	Email	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	date|email	Check box combined answer	Cleared to play on check boxes	Total number of No's	Cleared to play on answers	combined answers	CLEARED TO PLAY
2	#############	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		I have read the city of Toronto NoticeI acknowledge I have not answered YES to any of the above questions	TRUE	10	TRUE	TRUETRUE	TRUE
3	#############	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		I have read the city of Toronto NoticeI have answered YES, and will not attend baseball activities today	FALSE	9	FALSE	FALSEFALSE	FALSE
4	#############	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		I have read the city of Toronto NoticeI acknowledge I have not answered YES to any of the above questions	TRUE	9	FALSE	TRUEFALSE	FALSE
5	#############	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		I have read the city of Toronto NoticeI acknowledge I have not answered YES to any of the above questions	TRUE	10	TRUE	TRUETRUE	TRUE
6	#############	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		I have read the city of Toronto NoticeI have answered YES, and will not attend baseball activities today	FALSE	10	TRUE	FALSETRUE	FALSE
7	#############	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		I have read the city of Toronto NoticeI acknowledge I have not answered YES to any of the above questions	TRUE	10	TRUE	TRUETRUE	TRUE
8	#############	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		I have read the city of Toronto NoticeI acknowledge I have not answered YES to any of the above questions	TRUE	10	TRUE	TRUETRUE	TRUE
9	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		00	FALSE	0	FALSE	FALSEFALSE	FALSE
DATA

Cell Formulas
Range	Formula
A2:Q9	A2	='Form responses 1'!A2
S2:S9	S2	=P2&Q2
T2:T9	T2	=ISNUMBER(SEARCH("I have read the city of Toronto NoticeI acknowledge I have not answered YES to any of the above questions",S2))
U2:U9	U2	=COUNTIFS(F2:O2,"no")
V2:V9	V2	=ISNUMBER(SEARCH("10",U2))
W2:W9	W2	=T2&V2
X2:X9	X2	=ISNUMBER(SEARCH("TRUETRUE",W2))

 

[Joe4]

OK, in looking at this sheet, what details should the formula in D3 be using?
- The date from A1 or B1?
- The Email address from cell B2 or C2?

Is there any other column (other than column D) to check the other Email address?

 

[robgoldstein]

OK, in looking at this sheet, what details should the formula in D3 be using?
- The date from A1 or B1?
- The Email address from cell B2 or C2?

Is there any other column (other than column D) to check the other Email address?

it is using the date in B1. I have a formula in B2 that i will eventually move over, but I need the dates that already input to check it.
We need email in B2. I will then duplicate this formula for C2 and then combine the results for a final result.

 

[Joe4]

OK, use this formula for the first record (on row 3) and copy down for the rest:

=IF(COUNTIFS(Data!$B:$B,$B3,Data!$C:$C,$B$1)>0,"X","")