I am looking for a formula that will check 2 tabs for a matching name and date and then pull the corresponding data onto a new tab. For example look for John Smith in column B on tabs 1 and 2 and check for a matching date and if the name and dates match pull the row over to the tab with the formula.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Formula Help
- Thread starter datadummy
- Start date
Eric W
MrExcel MVP
- Joined
- Aug 18, 2015
- Messages
- 13,369
With these 2 tabs:
You can just use a FILTER like this:
| Book1 | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Name | Date | Something1 | Something2 | ||
| 2 | John | 1/1/2024 | 1 | a | ||
| 3 | John | 2/2/2024 | 2 | b | ||
| 4 | Jane | 3/3/2024 | 3 | c | ||
| 5 | Jane | 4/4/2024 | 4 | d | ||
| 6 | Jane | 5/5/2024 | 5 | e | ||
| 7 | Max | 6/6/2024 | 6 | f | ||
| 8 | ||||||
Tab1 | ||||||
| Book1 | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Name | Date | Something1 | Something2 | ||
| 2 | Jane | 3/3/2024 | 3 | c | ||
| 3 | Amy | 7/7/2024 | 7 | g | ||
| 4 | Jane | 5/5/2024 | 5 | e | ||
| 5 | Max | 6/6/2024 | 6 | f | ||
| 6 | John | 2/2/2024 | 2 | b | ||
| 7 | ||||||
Tab2 | ||||||
You can just use a FILTER like this:
| Book1 | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Name | Date | Something1 | Something2 | ||
| 2 | John | 2/2/2024 | 2 | b | ||
| 3 | Jane | 3/3/2024 | 3 | c | ||
| 4 | Jane | 5/5/2024 | 5 | e | ||
| 5 | Max | 6/6/2024 | 6 | f | ||
| 6 | ||||||
Tab3 | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A2:D5 | A2 | =FILTER('Tab1'!$A$2:$D$100,('Tab1'!$A$2:$A$100<>"")*(COUNTIFS('Tab2'!$A$2:$A$100,'Tab1'!$A$2:$A$100,'Tab2'!$B$2:$B$100,'Tab1'!$B$2:$B$100)),"N/A") |
| Dynamic array formulas. | ||
Upvote
0
