vlookup multiple data

[Art]

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!

 

[T. Valko]

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?

 

[Jacob Skaria]

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)

 

[Art]

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!!!

 

[Art]

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]

I would suggest you not do this.

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

 

[Art]

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!!!

 

[Art]

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!

 

[T. Valko]

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".

 

[Herbert Seidenberg]

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

 

[Art]

This link leads to a zip file I am asked to download. What is it? I am
curious if it is an idea, but I am leary about opening files.

 

[Herbert Seidenberg]

Excel 2007
A pdf preview
http://www.mediafire.com/file/mzyq20yze3k/03_06_10.pdf