Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup multiple data
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup multiple data
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup multiple data
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup multiple data
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! . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup multiple data
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! . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup multiple data
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! . . |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup multiple data
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! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup multiple data
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! . . |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup multiple data
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup multiple data
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" wrote: Excel 2007 Tables With macro http://c0718892.cdn.cloudfiles.racks.../03_06_10.xlsm . |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup multiple data
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup and multiple data | Excel Discussion (Misc queries) | |||
VLOOKUP From Multiple Data Sheets | Excel Worksheet Functions | |||
vlookup - return multiple data | Excel Discussion (Misc queries) | |||
Vlookup for multiple row data | Excel Worksheet Functions | |||
VLookup Multiple Data Rows | Excel Worksheet Functions |