vlookup multiple data
in [Functions]
|
Prev: Counting sales
Next: Formula help required
From: Art on 4 Mar 2010 23:11 Is it possible to have a cell contain two pieces of informaton (e.g., A1 = "Me, You"), a vlookup statement to look for each pece of informaton in that cell (e.g., first "Me", then "You") in another sheet, and display the match in B1? Sheet 1 A1 = "Me, You" B1 = (after vlookup) "Art, Joe" Sheet 2 A1 = "Me" and B1 = "Art" A2 = "You" and B2 = "Joe" I tried all different kinds of =VLOOKUP with different functions, and I can't figure t out, of course I assume its possble to even use VLOOKUP in this way. Perhaps I need to use a macro? Thanks!
From: T. Valko on 4 Mar 2010 23:45 > Sheet 1 > A1 = "Me, You" > B1 = (after vlookup) "Art, Joe" > > Sheet 2 > A1 = "Me" and B1 = "Art" > A2 = "You" and B2 = "Joe" Does your data actually contain all those quotes? -- Biff Microsoft Excel MVP "Art" <Art(a)discussions.microsoft.com> wrote in message news:C765661D-55A7-4194-8C88-BA3EEB233B99(a)microsoft.com... > Is it possible to have a cell contain two pieces of informaton (e.g., A1 = > "Me, You"), a vlookup statement to look for each pece of informaton in > that > cell (e.g., first "Me", then "You") in another sheet, and display the > match > in B1? > > Sheet 1 > A1 = "Me, You" > B1 = (after vlookup) "Art, Joe" > > Sheet 2 > A1 = "Me" and B1 = "Art" > A2 = "You" and B2 = "Joe" > > I tried all different kinds of =VLOOKUP with different functions, and I > can't figure t out, of course I assume its possble to even use VLOOKUP in > this way. Perhaps I need to use a macro? > > Thanks!
From: Jacob Skaria on 5 Mar 2010 00:38 Try =VLOOKUP(LEFT(A1,FIND(",",A1)-1),Sheet2!A:B,2,0)&", " & VLOOKUP(TRIM(MID(A1,FIND(",",A1)+1,255)),Sheet2!A:B,2,0) -- Jacob "Art" wrote: > Is it possible to have a cell contain two pieces of informaton (e.g., A1 = > "Me, You"), a vlookup statement to look for each pece of informaton in that > cell (e.g., first "Me", then "You") in another sheet, and display the match > in B1? > > Sheet 1 > A1 = "Me, You" > B1 = (after vlookup) "Art, Joe" > > Sheet 2 > A1 = "Me" and B1 = "Art" > A2 = "You" and B2 = "Joe" > > I tried all different kinds of =VLOOKUP with different functions, and I > can't figure t out, of course I assume its possble to even use VLOOKUP in > this way. Perhaps I need to use a macro? > > Thanks!
From: Art on 5 Mar 2010 10:56 Thanks, Jacob! What is there is more than two items? Sheet 1 A1 = "Me, You" B1 = (after vlookup) "Art, Joe" Sheet 2 A1 = "Me" and B1 = "Art" A2 = "You" and B2 = "Joe" A3 = "Us" and B2 = "Peter" A4 = "Her" and B2 = "Janet" A5 = "Them" and B2 = "Oscar" A6 = "Him" and B2 = "Henry" Sheet1A1 could also equal "Me, You, Her, Him" If this was the case, Sheet1B1 should display "Art, Joe, Janet, Henry" (And, of course, there are no quotes. I just used them here to indicate the actual values of the cells.) Can there be a loop-of-sorts to use the vlookup for multiple commas in Sheet1A1? Thanks!!! "Jacob Skaria" wrote: > Try > > =VLOOKUP(LEFT(A1,FIND(",",A1)-1),Sheet2!A:B,2,0)&", " & > VLOOKUP(TRIM(MID(A1,FIND(",",A1)+1,255)),Sheet2!A:B,2,0) > > -- > Jacob > > > "Art" wrote: > > > Is it possible to have a cell contain two pieces of informaton (e.g., A1 = > > "Me, You"), a vlookup statement to look for each pece of informaton in that > > cell (e.g., first "Me", then "You") in another sheet, and display the match > > in B1? > > > > Sheet 1 > > A1 = "Me, You" > > B1 = (after vlookup) "Art, Joe" > > > > Sheet 2 > > A1 = "Me" and B1 = "Art" > > A2 = "You" and B2 = "Joe" > > > > I tried all different kinds of =VLOOKUP with different functions, and I > > can't figure t out, of course I assume its possble to even use VLOOKUP in > > this way. Perhaps I need to use a macro? > > > > Thanks!
From: Art on 5 Mar 2010 11:00
No, the quotes are used just to indicate the actual values of the cells. I replied to Jacob also asking if the vlookup could do more than 2 items, too. For example Sheet 1 A1 = "Me, You, Him, Her" B1 = (after vlookup) "Art, Joe" Sheet 2 A1 = "Me" and B1 = "Art" A2 = "You" and B2 = "Joe" A3 = "Us" and B2 = "Patrice" A4 = "Him" and B2 = "Mike" A5 = "Them" and B2 = "Oscar" A6 = "Her" and B2 = "Janet" Then Sheet1B1 should display "Art, Joe, Mike, Janet". Thanks! "T. Valko" wrote: > > Sheet 1 > > A1 = "Me, You" > > B1 = (after vlookup) "Art, Joe" > > > > Sheet 2 > > A1 = "Me" and B1 = "Art" > > A2 = "You" and B2 = "Joe" > > Does your data actually contain all those quotes? > > -- > Biff > Microsoft Excel MVP > > > "Art" <Art(a)discussions.microsoft.com> wrote in message > news:C765661D-55A7-4194-8C88-BA3EEB233B99(a)microsoft.com... > > Is it possible to have a cell contain two pieces of informaton (e.g., A1 = > > "Me, You"), a vlookup statement to look for each pece of informaton in > > that > > cell (e.g., first "Me", then "You") in another sheet, and display the > > match > > in B1? > > > > Sheet 1 > > A1 = "Me, You" > > B1 = (after vlookup) "Art, Joe" > > > > Sheet 2 > > A1 = "Me" and B1 = "Art" > > A2 = "You" and B2 = "Joe" > > > > I tried all different kinds of =VLOOKUP with different functions, and I > > can't figure t out, of course I assume its possble to even use VLOOKUP in > > this way. Perhaps I need to use a macro? > > > > Thanks! > > > . > |