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?

 

[robgoldstein]

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","")

hmm. When I past it in cell D3 on my team page it is not returning anything. just a blank cell.

 

[Joe4]

hmm. When I past it in cell D3 on my team page it is not returning anything. just a blank cell.

Yes, that is what you said it is supposed to return if it does not find a match.

If it is not working, make sure that the values you are matching on are all the same. You can only match numbers-to-numbers, dates-to-dates, and text-to-text.
If you try matching a valid date to a date entered as a string, it will not match.

 

[robgoldstein]

OK. so I got it working a bit, but it is not pulling the info from cell x on the data sheet of the line that matches the email and date on the team page.

 

[Joe4]

My bad! I thought you literally wanted to return the letter "X", and not whatever is in column X.

I think we may need to use an Array formula (meaning you would need to enter with CTRL+SHIFT+ENTER and not just ENTER).
Using this link here: Two-column Lookup in Excel, I come up with this formula:

{=INDEX(Data!$X$2:$X$9,MATCH($B3&$B$1,Data!$B$2:$B$9&Data!$C$2:$C$9,0))}

Note that the { and } signs are really not part of the formula, that just indicates the formula was entered with CTRL+SHIFT+ENTER.

If you want to suppress the #N/A errors that occur when a match is not found, use this version:

{=IFERROR(INDEX(Data!$X$2:$X$8,MATCH($B3&$B$1,Data!$B$2:$B$6&Data!$C$2:$C$6,0)),"")}

 

[robgoldstein]

on the data sheet of the line that matches the email and date on

My bad! I thought you literally wanted to return the letter "X", and not whatever is in column X.

I think we may need to use an Array formula (meaning you would need to enter with CTRL+SHIFT+ENTER and not just ENTER).
Using this link here: Two-column Lookup in Excel, I come up with this formula:

{=INDEX(Data!$X$2:$X$9,MATCH($B3&$B$1,Data!$B$2:$B$9&Data!$C$2:$C$9,0))}

Note that the { and } signs are really not part of the formula, that just indicates the formula was entered with CTRL+SHIFT+ENTER.

If you want to suppress the #N/A errors that occur when a match is not found, use this version:

{=IFERROR(INDEX(Data!$X$2:$X$8,MATCH($B3&$B$1,Data!$B$2:$B$6&Data!$C$2:$C$6,0)),"")}

that works like magic.

 

[Joe4]

You are welcome.

Note: When marking a post as the solution, you want to mark the post that has the solution (not the post acknowledging that a solution has been received).
I have made that update for you.