I need a look up to pull names from a list that don't 100% match?

[t0mrogers]

Hi,

I am trying to add some kind of look up function to pull the names from the list in columns N and O, when I dump data into cells A2 to I14.

I need it to match the names in columns O and F and pull the corresponding Site from column N into column J, but because the names don't 100% match due to the . in the middle of the names in column F I am struggling.

Hopefully, I have explained this correctly.

Any help on this would be greatly appreciated.


Many thanks

Tom

[Pete_UK]

You can use this in J2:

=INDEX(N:N,MATCH(SUBSTITUTE(F2,"."," "),O:O,0))

then copy down. It changes the full-stops to spaces, so that you can find a match.

Hope this helps.

Pete

[davsth]

possibly
=INDEX($N$2:$N$23,MATCH(SUBSTITUTE(F2,"."," "),$O$2:$O$23,0))

match by the value with a . replaced by a space

[t0mrogers]

Thanks for the quick replies, they both work fine.

Your help is greatly appreciated.

Many many thanks,
Tom

[wk9128]

Try

=LOOKUP(,-SEARCH(SUBSTITUTE(F2,"."," "),$O$2:$O$23),$N$2:$N$23)