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



## zone709 (Dec 14, 2022)

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*


----------



## hajiali (Dec 14, 2022)

Can you post the sheet names Timesheet. What the results you are expecting.


----------



## Fluff (Dec 15, 2022)

How about
	
	
	
	
	
	



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


----------



## zone709 (Dec 15, 2022)

Fluff said:


> How about
> 
> 
> 
> ...


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*

 D​E​*6*​​​*7*​*ID*​*JOB*​*8*​*1*​*225 12 Street*​*9*​*15*​*225 12 Street*​*10*​*10*​*205 5th street*​*11*​*12*​*Place Street*​*12*​*16*​*225 12 Street*​*13*​*23*​*225 12 Street*​*14*​*15*​*225 12 Street*​*15*​*25*​*225 12 Street*​*16*​*13*​*225 12 Street*​*17*​*12*​*225 12 Street*​*18*​*49*​*225 12 Street*​*19*​*62*​*225 12 Street*​

Sheet: *Timesheet*


----------



## Fluff (Dec 15, 2022)

Can you please post data from both sheets using the XL2BB add-in.


----------



## zone709 (Dec 15, 2022)

Fluff said:


> Can you please post data from both sheets using the XL2BB add-in.


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*

 F​G​H​I​*1*​*2*​*3*​1​*4*​15​*5*​16​*6*​23​*7*​15​*8*​25​*9*​13​*10*​12​*11*​F11225 12 Street*12*​F12FormulaShould return numbers 49 & 62 from timesheet. As they are not in column H

Sheet: *Sheet1*


Timesheet from other Tab

*Excel 2016 (Windows) 32 bit*

 D​E​*5*​​​*6*​​​*7*​*ID*​*JOB*​*8*​*1*​*225 12 Street*​*9*​*15*​*225 12 Street*​*10*​*10*​*205 5th street*​*11*​*12*​*Place Street*​*12*​*16*​*225 12 Street*​*13*​*23*​*225 12 Street*​*14*​*15*​*225 12 Street*​*15*​*25*​*225 12 Street*​*16*​*13*​*225 12 Street*​*17*​*12*​*225 12 Street*​*18*​*49*​*225 12 Street*​*19*​*62*​*225 12 Street*​

Sheet: *Timesheet*


----------



## Fluff (Dec 15, 2022)

Can you please use the XL2BB add-in, not Forum Tools or what ever you are using.


----------



## zone709 (Dec 15, 2022)

Fluff said:


> Can you please use the XL2BB add-in, not Forum Tools or what ever you are using.


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?


----------



## Fluff (Dec 15, 2022)

Select the range & click the Mini-sheet option.


----------



## zone709 (Dec 15, 2022)

Fluff said:


> Select the range & click the Mini-sheet option.


ok its not making me click the Mini Sheet tab after i select range. Not sure why.


----------



## zone709 (Dec 14, 2022)

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*


----------



## Fluff (Dec 15, 2022)

It doesn't make you do anything, you need to click Mini Sheet


----------



## zone709 (Dec 15, 2022)

it doesn't make me click it. The tab is not highlighted to work. Do i need to do something in excel so i can click Mini Sheet?


----------



## Fluff (Dec 15, 2022)

Have you tried what it says in this post XL2BB Disappears when Excel is Reopened


----------



## zone709 (Dec 15, 2022)

Yes and my tab is always there Xl2bb. It never disappears. I just cant click on Mini Sheet or Table Only


----------



## Fluff (Dec 15, 2022)

But have you put the file in a trusted location and/or unblocked it?


----------



## zone709 (Dec 15, 2022)

I been trying everything. I even went to File Explorer and right click No general Tab. I am either doing something wrong or its something else. Is there walk through instructions on this. I already clicked the link you sent me and i went through those steps.


----------



## Fluff (Dec 15, 2022)

Try 
	
	
	
	
	
	



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


----------



## zone709 (Dec 15, 2022)

Fluff said:


> Try
> 
> 
> 
> ...


Ok this formula works thanks very Much. Now i am going to try and figure out how to get this Mini Sheet to work.


----------



## Fluff (Dec 15, 2022)

You're welcome & thanks for the feedback.


----------



## zone709 (Dec 15, 2022)

Hi sorry if it doesn't find a number missing. I get in return #CALC!. Anyway for this not to happen and just to return blank?


----------



## zone709 (Dec 14, 2022)

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*


----------



## Fluff (Dec 15, 2022)

How about
	
	
	
	
	
	



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


----------



## zone709 (Dec 16, 2022)

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


----------



## Fluff (Dec 16, 2022)

What should those other columns check against?


----------



## zone709 (Dec 16, 2022)

Fluff said:


> 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.


----------



## Fluff (Dec 16, 2022)

Sorry, that makes no sense to me.
The criteria for col E is G11, so what is the criteria for the other columns?


----------



## zone709 (Dec 16, 2022)

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*


----------



## Fluff (Dec 16, 2022)

Try
	
	
	
	
	
	



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


----------



## zone709 (Dec 16, 2022)

Perfect thank you. I didn't know i have to add it like that hanks.


----------



## Fluff (Dec 16, 2022)

My pleasure.


----------

