vlookup multiple data

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!

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" wrote in message
...
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!

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!

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!

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" wrote in message
...
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!


.

I would suggest you not do this.

Why don't you just put each lookup value in its own cell?

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
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" wrote in message
...
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!


.

I thought about looking at the information from different perspectives. I
have this "issue" on my sheet listing all of the textbooks we use at the
college. There are well over 300. For most, its a one course for each
textbook, but there are a number of instances where a book is used with more
than one course. So, instead of having duplicates listings for textbooks, I
have a cell in that textbook row that includes each course ID to which that
text is assigned. For example,

B = Textbook title, AA1 = course ID(s), AB1 = course title(s)

B1 = Abnormal Psychology: An Integrative Approach
AA1 = PSY 275
AB1 = Abnormal Psychology

B4 = Accounting Principles
AA4 = ACC 255, ACC 355
AB4 = "Accounting I, Accounting II"

B8 = On Food and Cooking: Science and Lore of the Kitchen
AA8 = CUL 116, CUL 117, CUL 118
AB8 = Culinary Arts I, Culinary Arts II, Culinary Arts III

Ideally, I'd like AB to be auto-populated after a user enters the course IDs
in AA. its less likely they will make a typing error entering a course ID
than typing in the course name. Auto-populating AB will also help the user
know they entered a valid course ID.

The course IDs and course titles are entered manually on a separate sheet in
the workbook. Another user is responsible for maintaining that information,
and, unfortunately, course titles can change. So, to avoid having consistency
errors across departments, I wanted to have the course titles linked so it is
updated automatically if the one user changes it on another sheet.

I also added a sheet for course developers, who have a cell that counts the
number of textbooks assigned to a course. For example, if Culinary I was
being developed/revised, the course developers work keep track of the
development on their sheet and see information linked to the Courses sheet
and the Textbook sheet (i.e., # of textbooks assigned to that course). The
counting works perfectly, even when there is more than one course listed in
the AA cell.

Any thoughts?

Thanks!!!




"T. Valko" wrote:

I would suggest you not do this.

Why don't you just put each lookup value in its own cell?

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
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" wrote in message
...
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!


.



.

Your formula worked perfectly! Any thoughts on how it could be modified for
more than two (e.g., BUS 280, ECO 110, PSY 343)?

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!

Well, I'm not sure I follow you on this but there has to be a better way
then concatenating a bunch of lookups as you describe. After 2 or 3 lookups
the formula would be very long an "unruly".

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
I thought about looking at the information from different perspectives. I
have this "issue" on my sheet listing all of the textbooks we use at the
college. There are well over 300. For most, its a one course for each
textbook, but there are a number of instances where a book is used with
more
than one course. So, instead of having duplicates listings for textbooks,
I
have a cell in that textbook row that includes each course ID to which
that
text is assigned. For example,

B = Textbook title, AA1 = course ID(s), AB1 = course title(s)

B1 = Abnormal Psychology: An Integrative Approach
AA1 = PSY 275
AB1 = Abnormal Psychology

B4 = Accounting Principles
AA4 = ACC 255, ACC 355
AB4 = "Accounting I, Accounting II"

B8 = On Food and Cooking: Science and Lore of the Kitchen
AA8 = CUL 116, CUL 117, CUL 118
AB8 = Culinary Arts I, Culinary Arts II, Culinary Arts III

Ideally, I'd like AB to be auto-populated after a user enters the course
IDs
in AA. its less likely they will make a typing error entering a course ID
than typing in the course name. Auto-populating AB will also help the user
know they entered a valid course ID.

The course IDs and course titles are entered manually on a separate sheet
in
the workbook. Another user is responsible for maintaining that
information,
and, unfortunately, course titles can change. So, to avoid having
consistency
errors across departments, I wanted to have the course titles linked so it
is
updated automatically if the one user changes it on another sheet.

I also added a sheet for course developers, who have a cell that counts
the
number of textbooks assigned to a course. For example, if Culinary I was
being developed/revised, the course developers work keep track of the
development on their sheet and see information linked to the Courses sheet
and the Textbook sheet (i.e., # of textbooks assigned to that course). The
counting works perfectly, even when there is more than one course listed
in
the AA cell.

Any thoughts?

Thanks!!!




"T. Valko" wrote:

I would suggest you not do this.

Why don't you just put each lookup value in its own cell?

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
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" wrote in message
...
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!


.



.

Excel 2007 Tables
With macro
http://c0718892.cdn.cloudfiles.racks.../03_06_10.xlsm