Vlookup search within a cell if the lookup_value is not in the same order as the arr

Hi,

If you have heaps of similar things that need to be allowed for it may be easier to redo one of your lists but...
I had a play & although it's not pretty the below seemed to work for your specific example (ie for 2 word names separated by a space) by using wildcards in the lookup value, and checking that both lookups give the same result (it's not bulletproof but it may be rough enough for your needs).

1)
in cell B3 type:
=VLOOKUP("*"&LEFT(A3,FIND(" ",A3,1)-1)&"*",$A$12:$B$17,1,FALSE)
in cell C3
=IF(B3=VLOOKUP("*"&MID(A3,FIND(" ",A3,1)+1,LEN(A3)-FIND(" ",A3,1)+1)&"*",$A$12:$B$17,1,FALSE),B3,"no match")

2)
or in a single cell but this may be harder/slower on the system if you are using a lot of vlookups:
=IF(VLOOKUP("*"&LEFT(A3,FIND(" ",A3,1)-1)&"*",$A$12:$B$17,1,FALSE)=VLOOKUP("*"&MID(A3,FIND(" ",A3,1)+1,LEN(A3)-FIND(" ",A3,1)+1)&"*",$A$12:$B$17,1,FALSE),VLOOKUP("*"&LEFT(A3,FIND(" ",A3,1)-1)&"*",$A$12:$B$17,1,FALSE),"no match")

To lookup other columns just change the bold "1" in option 2.
the comparison check between the two vlookups could use a different column in either of the options but I am assuming that there is less likely to be results found in the Names column than there may be in other columns.

hth
Rob