Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. * |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup same or similar | Excel Worksheet Functions | |||
vlookup with similar lookup values | Excel Discussion (Misc queries) | |||
lookup or some other type of similar function | Excel Programming | |||
Using the lookup Function for similar items to add together | Excel Worksheet Functions | |||
Lookup or similar | Excel Worksheet Functions |