Formula help. Match name return numbers that not in Column H

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,073
Office Version
  1. 365
Platform
  1. Windows
Trying to match the name in G11 to the name on the other tab called Timesheet in column E:E. If matches, then return the numbers in column D:D on the Timesheet tab that are not in Column H below. I need to know what numbers in H are missing from the Timesheet Tab from Column D:D if the names Match in G11 verse the timesheet column E:E. Hope I explain it right. Any help Apricated. Not sure the best way to do this has i will probably have more then one number returning for results or none.

Excel 2016 (Windows) 32 bit
F
G
H
1
2
3
1​
4
15​
5
16​
6
23​
7
15​
8
25​
9
13​
10
12​
11
F11225 12 Street
12
F12Formula here
Sheet: Sheet1
 
How about
Excel Formula:
=LET(f,FILTER(Sheet2!D2:D1000,Sheet2!E2:E1000=G11,""),FILTER(f,ISNA(MATCH(f,H3:H10,0)),""))
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hi Is there anyway to add more columns to this formula to check more then E2:E1000. The D2:D1000 still stays the same.

Like this E2:E1000 G2:G1000 I2:I2000 etc. How can i add this to this formula if i can in this part --- Sheet2!E2:E1000=G11,""
 
Upvote 0
What should those other columns check against?
 
Upvote 0
What should those other columns check against?
The same in the Formula H3:H11 if the names match. So like E G I check verse H if name matches. Instead of just E on the original.
 
Upvote 0
Sorry, that makes no sense to me.
The criteria for col E is G11, so what is the criteria for the other columns?
 
Upvote 0
Look at the first job that in column E way below, Then there is Jobs in f and g. So if E doesn't match G11 maybe F and G will and return the number in D on timesheet that isn't in column H.

Instead of me doing this below over and over.

=LET(f,FILTER(Timesheet!D2:D1000,Timesheet!E2:E1000=G11,""),FILTER(f,ISNA(MATCH(f,H3:H10,0)),""))

=LET(f,FILTER(Timesheet!D2:D1000,Timesheet!F2:F1000=G11,""),FILTER(f,ISNA(MATCH(f,H3:H10,0)),""))

=LET(f,FILTER(Timesheet!D2:D1000,Timesheet!G2:G1000=G11,""),FILTER(f,ISNA(MATCH(f,H3:H10,0)),""))

Trying to add something like this if possible.
=LET(f,FILTER(Timesheet!D2:D1000,Timesheet!E2:E1000,F2:F1000,G2:G1000=G11,""),FILTER(f,ISNA(MATCH(f,H3:H10,0)),""))
But that's not working.



Excel 2016 (Windows) 32 bit
D
E
F
G
7
ID
JOB
8
1
11th street
205 5th street
225 12 Street
Sheet: Timesheet
 
Upvote 0
Try
Excel Formula:
=LET(f,FILTER(Timesheet!D2:D1000,(Timesheet!E2:E1000=G11)+(Timesheet!F2:F1000=G11)+(Timesheet!G2:G1000=G11),""),FILTER(f,ISNA(MATCH(f,H3:H10,0)),""))
 
Upvote 0
Solution
Perfect thank you. I didn't know i have to add it like that hanks.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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
Back
Top