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

Z

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,125
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
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
1​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
2​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
3​
[/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
4​
[/td][td][/td][td][/td][td]
15​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
5​
[/td][td][/td][td][/td][td]
16​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
6​
[/td][td][/td][td][/td][td]
23​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
7​
[/td][td][/td][td][/td][td]
15​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
8​
[/td][td][/td][td][/td][td]
25​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
9​
[/td][td][/td][td][/td][td]
13​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
10​
[/td][td][/td][td][/td][td]
12​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
11​
[/td][td]F11[/td][td]225 12 Street[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
12​
[/td][td]F12[/td][td=bgcolor:#FFFF00]Formula here[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Can you post the sheet names Timesheet. What the results you are expecting.
 
Upvote 0
How about
Excel Formula: 
=FILTER(H3:H11,ISNA(MATCH(H3:H11,FILTER(Sheet1!D2:D1000,Sheet1!E2:E1000=G11),0)))
 
Upvote 0
Fluff said:
How about
Excel Formula: 
=FILTER(H3:H11,ISNA(MATCH(H3:H11,FILTER(Sheet1!D2:D1000,Sheet1!E2:E1000=G11),0)))
Click to expand...
Hi so i changed it to Timesheet which where D:D and E:E are , but it returns 0. It should of returned 2 numbers that were missing from column H.

=FILTER(H3:H11,ISNA(MATCH(H3:H11,FILTER(Timesheet!D2:D1000,Timesheet!E2:E1000=G11),0)))

If you see the last 2 numbers 49 & 62 should of returned because they are not in Column H and column E matches G11 address 225 21 Street.

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
D​
[/th][th]
E​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
6​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
7​
[/td][td]
ID​
[/td][td]
JOB​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
8​
[/td][td]
1​
[/td][td=bgcolor:#00B0F0]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
9​
[/td][td=bgcolor:#FFFF00]
15​
[/td][td=bgcolor:#00B0F0]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
10​
[/td][td]
10​
[/td][td=bgcolor:#F8CBAD]
205 5th street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
11​
[/td][td=bgcolor:#FFFF00]
12​
[/td][td=bgcolor:#BFBFBF]
Place Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
12​
[/td][td]
16​
[/td][td=bgcolor:#00B0F0]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
13​
[/td][td]
23​
[/td][td=bgcolor:#00B0F0]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
14​
[/td][td=bgcolor:#FFFF00]
15​
[/td][td=bgcolor:#00B0F0]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
15​
[/td][td]
25​
[/td][td=bgcolor:#00B0F0]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
16​
[/td][td]
13​
[/td][td=bgcolor:#00B0F0]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
17​
[/td][td=bgcolor:#FFFF00]
12​
[/td][td=bgcolor:#00B0F0]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
18​
[/td][td=bgcolor:#00B050]
49​
[/td][td=bgcolor:#00B050]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
19​
[/td][td=bgcolor:#00B050]
62​
[/td][td=bgcolor:#00B050]
225 12 Street​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Timesheet[/td][/tr][/table]
 
Upvote 0
Can you please post data from both sheets using the XL2BB add-in.
 
Upvote 0
Fluff said:
Can you please post data from both sheets using the XL2BB add-in.
Click to expand...
I am just looking for numbers that are not in Column H, but are in Column D from timesheet if address matches next to it. Doesnt matter how many times the numbers are on the timesheet. Just need to know what i am missing.

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
1​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
2​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
3​
[/td][td][/td][td][/td][td]
1​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
4​
[/td][td][/td][td][/td][td]
15​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
5​
[/td][td][/td][td][/td][td]
16​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
6​
[/td][td][/td][td][/td][td]
23​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
7​
[/td][td][/td][td][/td][td]
15​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
8​
[/td][td][/td][td][/td][td]
25​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
9​
[/td][td][/td][td][/td][td]
13​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
10​
[/td][td][/td][td][/td][td]
12​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
11​
[/td][td]F11[/td][td]225 12 Street[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
12​
[/td][td]F12[/td][td=bgcolor:#FFFF00]Formula[/td][td][/td][td]Should return numbers 49 & 62 from timesheet. As they are not in column H[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]


Timesheet from other Tab

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
D​
[/th][th]
E​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
5​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
6​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
7​
[/td][td]
ID​
[/td][td]
JOB​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
8​
[/td][td]
1​
[/td][td=bgcolor:#00B0F0]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
9​
[/td][td=bgcolor:#FFFF00]
15​
[/td][td=bgcolor:#00B0F0]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
10​
[/td][td]
10​
[/td][td=bgcolor:#F8CBAD]
205 5th street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
11​
[/td][td=bgcolor:#FFFF00]
12​
[/td][td=bgcolor:#BFBFBF]
Place Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
12​
[/td][td]
16​
[/td][td=bgcolor:#00B0F0]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
13​
[/td][td]
23​
[/td][td=bgcolor:#00B0F0]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
14​
[/td][td=bgcolor:#FFFF00]
15​
[/td][td=bgcolor:#00B0F0]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
15​
[/td][td]
25​
[/td][td=bgcolor:#00B0F0]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
16​
[/td][td]
13​
[/td][td=bgcolor:#00B0F0]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
17​
[/td][td=bgcolor:#FFFF00]
12​
[/td][td=bgcolor:#00B0F0]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
18​
[/td][td=bgcolor:#00B050]
49​
[/td][td=bgcolor:#00B050]
225 12 Street​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
19​
[/td][td=bgcolor:#00B050]
62​
[/td][td=bgcolor:#00B050]
225 12 Street​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Timesheet[/td][/tr][/table]
 
Upvote 0
Can you please use the XL2BB add-in, not Forum Tools or what ever you are using.
 
Upvote 0
Fluff said:
Can you please use the XL2BB add-in, not Forum Tools or what ever you are using.
Click to expand...
I have copy range under Forum Tools. When i go under tab Xl2bb in the section Capture Range. It doesn't make me click Mini Sheet or Table Only if that's what you are looking for me to do?
 
Upvote 0
Select the range & click the Mini-sheet option.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
return value based on the number that matches any number in three columns
Replies
3
Views
143
MMM_84
M
C
  • Question Question
Comparing 3 tables to a list of numbers, if match, return 4 columns worth of data on another tab
Replies
4
Views
363
alansidman
A
E
  • Question Question
Formula to look at Column G&H in groups of data & returns the number repeated the most in each group.
Replies
4
Views
269
Event2020
E
U
  • Solved
Index formula with matching - Can it look at rows and columns?
Replies
2
Views
388
usnapoleon
U
E
  • Question Question
Exel Filter formula with multiple criterias
Replies
2
Views
277
Fluff
Fluff

Forum statistics

Threads
1,223,889
Messages
6,175,226
Members
452,620
Latest member
dsubash

Share this page

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