Is it possible to do a v-lookup using similar data?
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. |
Is it possible to do a v-lookup using similar data?
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 On Jul 22, 12:50*pm, madmmurphy wrote: 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. * |
Is it possible to do a v-lookup using similar data?
Sure just send it to the email address above.
--JP On Jul 22, 4:42*pm, madmmurphy wrote: 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" wrote: 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)) |
All times are GMT +1. The time now is 09:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com