Filter question

[angleright]

I am trying to filter on a column(column "L",Plan No) with the following formula
=IF(R18="","",FILTER(FILTER($H$2:$N$109,($L$2:$L$109=$R$18),"NOTHING FOUND"),{0,0,0,0,0,0,1}))
the filter returns work great if the cell contains a number and letter but returns nothing if the cell only contains a number. I have the cells formatted as text. I've tried formatting as general and numeric but still get the same results.
the formula is in row w59

 

[angleright]

thanks for your help, still the same result. It's making my hair hurt. Dont't understand why it works for you but not me.

 

[Fluff]

In that case can you upload the file the file to a share site such as OneDrive, GoogleDrive. Mark for sharing & then post the link you are given.

 

[angleright]

Hope this works, thanks

BETA LINKED MASTER FILE.xlsm

drive.google.com

 

[Fluff]

Thanks for that. The reason it's not working is that the filter formula is not looking at all the data, try

=IF(R18="","",FILTER(N2:N7000,L2:L7000=IFERROR(--R18,R18),"NOTHING FOUND"))

 

[angleright]

Great, that works but it affects the formula in r60. (see attached link) it should return like cell r30.

BETA POST.xlsm

drive.google.com

Thanks so much

 

[Fluff]

How about

=IF(R18="","",FILTER(H2:H7000,L2:L7000=IFERROR(--R18,R18),"NOTHING FOUND"))

 

[angleright]

Wow, i feel i'm getting close, however it doesn't fill in below. Sometimes there is the same plan number but in a different box, so it needs to show the box number for each instance. I am also planning to add the street no and street name to the return. if you look at r30, at the end of the formula i can change which columns get returned by changing the true false(0 or 1) to get what I need. is there anyway to do that with your formula in cell r60. Can't thank you enough.

BETA POST.xlsm

drive.google.com

 

[Fluff]

You could use

=IF(R18="","",CHOOSECOLS(FILTER(H2:N7000,L2:L7000=IFERROR(--R18,R18),"NOTHING FOUND"),1))

and change the 1 at the end to reflect which column you want.

 

[angleright]

that didn't work also i will be wanting to return values from columns h,j and k

 

[Fluff]

In that case use

=IF(R18="","",CHOOSECOLS(FILTER(H2:N7000,L2:L7000=IFERROR(--R18,R18),"NOTHING FOUND"),1,3,4))