From: Art on
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
> 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
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
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
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!
>
>
> .
>
 |  Next  |  Last
Pages: 1 2 3
Prev: Counting sales
Next: Formula help required