Is it possible to do a v-lookup using similar data?

[madmmurphy]

I am trying to use information from one database for another.
One data base lists the name as Jill Alexander, the other lists the name as
Alexander Jill. Can I do a v-lookup even though the data is entered
differently?

If so, please let me know how to do this.

 

[JP]

If the other lists only had last names in each cell, you could use
something like this:

=VLOOKUP(RIGHT(A1,LEN(A1)-FIND(" ",A1)),MyRange,2,FALSE)

Assuming "Jill Alexander" was in A1, and "Alexander" was in the
leftmost column of a named range "MyRange". This is because the first
argument of VLOOKUP can accept either a cell reference or a string
literal.

However, this (array) formula will look up the value in A1 (FirstName
LastName) and return the corresponding value in a named range
"MyRange" where it finds "LastName, Firstname" in a named range
"SearchRange"

=INDEX(MyRange,MATCH(1,(RIGHT(A1,LEN(A1)-FIND("
",A1))=LEFT(SearchRange,LEN(RIGHT(A1,LEN(A1)-FIND("
",A1)))))*(LEFT(A1,FIND(" ",A1)-1)=RIGHT(SearchRange,FIND(" ",A1)-1)),
0))

For example if you put "Jill Alexander" in A1 and had another block of
cells with "Alexander, Jill" the formula would find her reversed name
and return the corresponding value from the SearchRange range.

HTH,
JP

 

[madmmurphy]

Thank you for your help, but I am a bit confused.
Is it possible for me to send you a worksheet so I can see exactly what
youare taking about? it might make it more clear.

thanks

 

[JP]

Sure just send it to the email address above.

--JP