Search for 1 of 2 inputs and 3rd

robgoldstein

Board Regular
Joined
Oct 26, 2013
Messages
162
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Oct 26, 2013
Messages
162
Office Version
  1. 2019
Platform
  1. Windows
OK, use this formula for the first record (on row 3) and copy down for the rest:
Excel Formula:
=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.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,076
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 26, 2013
Messages
162
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,076
Office Version
  1. 365
Platform
  1. Windows
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:
Excel 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:
Excel Formula:
{=IFERROR(INDEX(Data!$X$2:$X$8,MATCH($B3&$B$1,Data!$B$2:$B$6&Data!$C$2:$C$6,0)),"")}
 
Solution

robgoldstein

Board Regular
Joined
Oct 26, 2013
Messages
162
Office Version
  1. 2019
Platform
  1. Windows
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:
Excel 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:
Excel Formula:
{=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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,076
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,147,734
Messages
5,742,865
Members
423,760
Latest member
photogfrog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top