vlookup multiple data
in [Functions]
|
Prev: Counting sales
Next: Formula help required
From: T. Valko on 5 Mar 2010 16:54 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" <Art(a)discussions.microsoft.com> wrote in message news:0E888125-B128-4DD7-B7E1-C3EEFF7F4174(a)microsoft.com... > 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! >> >> >> . >>
From: Art on 5 Mar 2010 17:57 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" <Art(a)discussions.microsoft.com> wrote in message > news:0E888125-B128-4DD7-B7E1-C3EEFF7F4174(a)microsoft.com... > > 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! > >> > >> > >> . > >> > > > . >
From: Art on 5 Mar 2010 20:58 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!
From: T. Valko on 5 Mar 2010 21:13 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" <Art(a)discussions.microsoft.com> wrote in message news:CFDCD0EE-FD0C-4769-8780-C0BF65403C13(a)microsoft.com... >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" <Art(a)discussions.microsoft.com> wrote in message >> news:0E888125-B128-4DD7-B7E1-C3EEFF7F4174(a)microsoft.com... >> > 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! >> >> >> >> >> >> . >> >> >> >> >> . >>
From: Herbert Seidenberg on 6 Mar 2010 12:25
Excel 2007 Tables With macro http://c0718892.cdn.cloudfiles.rackspacecloud.com/03_06_10.xlsm |