Find Text in a Woksheet

[ruvieira]

Hello All,

First post here :hi:

I'm a normal daily user but am completely stuck with this issue. Maybe someone can give me a hand?

I have a column AB5..AB7000 with names (that is dynamic and changes daily). I need to check in an array of names AF5..AL4000, if these first names exist.

So I'm looking for a formula to copy-paste in throughout the column AB. Simple? I thought so too...

Thanks in advance for the help.

Rui

[p45cal]

Confirmation needed:
AB5..AB7000 is one column.
AF5..AL4000 is 7 columns.
1. You're looking to check for the existence of each of the names in column AB in all of the cells in the 3996 x 7 range AF5:AL4000 block?
2. The comparison should be an exact match or the names should merely be present somewhere in a cell's contents?

[Aussiebear]

Please post a workbook so we can assist you?

[ruvieira]

1- Yes this correct.
2- I'm looking for an exact match in those 7 columns.

I didn't mention it in the original post but the output to the cells in column AB can be very simple, Just "Yes" or "No".

[p45cal]

re:"I'm looking for a formula to copy-paste in throughout the column AB"
I doubt you want a formula in column AB on top of the names, but say in cell AC5, you could put:
=COUNTIF($AF$5:$AL$4000,AB5)
and copy down, This will give you a value of 0 if the name is not found, and a positive number if it is found, being the number of times it has been found.
You could develop this further on the lines of:
=IF(COUNTIF($AF$5:$AL$4000,AB5)>0,"Exists","")
or
=IF(COUNTIF($AF$5:$AL$4000,AB5)>0,"Yes","No")