Search Function - Lookup from a cell in a range of cells

**scottymelloty** · 10-13-2012, 07:36 AM

Hi , thanks in advance for any help

say in CELL A1 i have the name Matt Richards

and in CELLS B1 - B10 i have a list of names , and one of them for example is Matthew Richards

I want to have a formula in Cell C1 that looks up Matt Richards in CELL A1 and see if that name appears in part in any name in the B1-10 list

any help to get me started much appreciated , thanks

**Ace_XL** · 10-13-2012, 07:41 AM

Try this..

=ISNUMBER(MATCH("*"&TRIM(LEFT(A1,FIND(" ",A1)))&"*"&TRIM(RIGHT(A1,LEN(A1)-FIND(" ",A1)))&"*",B1:B10,0))

**scottymelloty** · 10-13-2012, 08:16 AM

thanks, that works for the Matt Richards, two questions

1) Is there a way to get the result as the cell data it has found instead of saying TRUE
2) Can it be made to find closer matches , ie if i lookup up Andrew Bond its not associating that with Andy Bond

thanks again

**Ace_XL** · 10-13-2012, 08:20 AM

Is there a way to get the result as the cell data it has found instead of saying TRUE

=INDEX(B1:B10,MATCH("*"&TRIM(LEFT(A1,FIND(" ",A1)))&"*"&TRIM(RIGHT(A1,LEN(A1)-FIND(" ",A1)))&"*",B1:B10,0))

Can it be made to find closer matches , ie if i lookup up Andrew Bond its not associating that with Andy Bond

You would need to maintain a list of these close matches (Andy eaquals Andrew etc.) because Excel is not smart enough to guess that Andy equals Andrew

**scottymelloty** · 10-13-2012, 08:26 AM

that new formula just creates a blank cell for me ?
Good point on the andy and andrew , they have same surname though , any way of checking the surname matches from cell a1 in the B list , most of time the surnames will always match its the first name which has slight differences such as Andy and Andrew so i guess search for a match after the space from the first name to find an exact match would work ?

thanks for all the help !!