can't get =filter( to work

[still learning]

Hi

I have a table of names
A1:A100 = first names .. ( lower case)
B1:B100 = last names ... (upper case)
F1 = the first name I'm trying to match up .. (lower case)
I’m using a Filter Function because I have multiple same first names

=FILTER(B1:B100,A1:A100=F1,"not found")

The problem is if I type in the first name in F1, I get “not found”
If I copy and paste the same first name from the table, I get the last name (s)
I don’t understand it

mike

 

[awoohaw]

How did the names in the table get in the table? You may have a zero length or random space or non printing character in the table.
Do a LEN() function on the column in the table. If you have one or more longer characters in the LEN() function than what you see, then that is your problem.
If you cannot reasonably clean up your data then try this :

=FILTER(B1:B100,Left(A1:A100,Len(A1:A100)-1)=F1,"not found")

(i free formed that, I hope I have the right number of parens in there. And it only removes the last character, if the issue is extra spaces, you'll need to do a different function (substitute).

If that is the case you may want to do a left(len()-1) on column B as well. And you have to be sure all the fields in both columns have the strange character.
If that doesn't happen, you'll need to find the ASCII code and use the substitute function in there instead of the LEN() function.

You can also try to use the EXACT function to compare values.

 =EXACT(table[first name],F1)

 

[Fuji]

Did you try to confirm what you type in F1 = what you want to match?
e.g.
=F1=A1 where A1 contains what you want to match.

 

[Alex Blakenburg]

I just saw @awoohaw's post but will put this in anyway.
Where is your data coming from ? It is likely that you have additional invisible characters in your name fields.
I agree that you should really clean up the data in your name columns.

If it is just a space this should handle that:

=FILTER(B1:B100,TRIM(A1:A100)=F1,"not found")

This will handle more cases but it is essentially "contains" which may give you additional items in your results:

=FILTER(B1:B100,ISNUMBER(SEARCH(F1,A1:A100)),"not found")

 

[still learning]

Hi awoohaw
Some of The names were copied and pasted from a different sheet. I didn't enter them in that sheet
other names were typed in by me
Hi Fuji
First I typed in the first name in F1 and it didn't work, then I copied and pasted a name from A1:A100 and it did
Hi Alex
After reading your reply, I made a new sheet and typed in a few of the names ( I DIDN"T copy and paste them)
I then put in the my formula and IT WORKED
SO
I have to agree that some of the cells may have spaces between the letters that i can't see and/or the first names may not start at the left edge of the cell
I also tried both clean up formulas on the sheet i was having problems with and both work.

THANK YOU ALL for the help
You were all very helpful but I can only click on one check mark. So I will pick Alex because i'm going to use his formulas
But I would not have known what was wrong with out all of your help


mike

 

[Alex Blakenburg]

Appreciate you taking the time to acknowledge all contributors. It's often the sum of the conversations that gets us there.