Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create a multi column lookup list?
This is in Excel 2007.
In my 1st worksheet I have a list of books with 4 columns: ID, Title, Authors, Owner On a 2nd worksheet I have the following columns: ID, Checked out to, Date I want in the lookup list for ID in my 2nd worksheet to show the Title, authors and owner columns but only return the ID of the book. I can create a lookup list that contains only one column. Any ideas or suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create a multi column lookup list?
Hi Gabriel
on sheet2 in cell D2 enter =VLOOKUP($A2,Sheet1!$A:$D,2,0) This will return the Title, as it is in the second column of the range A:D on Sheet1 If you copy this across to columns E and F , changing the number to 2 and 3 respectively, you will get the information for Author and Owner You can make the number change automatically, by using the COLUMN() function. =COLUMN(B1) will return 2, COLUMN()C1) will return 3 etc. So Substituting this in the formula =VLOOKUP($A2,Sheet1!$A:$D,COLUMN(B1),0) means that you can just copy the formula across and it will automatically adjust for you. To prevent the formula returning errors when there is nothing in column A of sheet2, wrap it all in an IF statement =IF(A2="","",=VLOOKUP($A2,Sheet1!$A:$D,COLUMN(B1), 0)) Copy across through E2:F2 and copy D2:F2 down the sheet as far as required. -- Regards Roger Govier "Gabriel Lozano-Moran" wrote in message ... This is in Excel 2007. In my 1st worksheet I have a list of books with 4 columns: ID, Title, Authors, Owner On a 2nd worksheet I have the following columns: ID, Checked out to, Date I want in the lookup list for ID in my 2nd worksheet to show the Title, authors and owner columns but only return the ID of the book. I can create a lookup list that contains only one column. Any ideas or suggestions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create a multi column lookup list?
Hello Roger
thanks for your answer but what we need is actually a single list with multiple colomns. In Sheet2!$A I want to use this multi column lookup list so when the list pops up you get to see the 3 columns and when you select an entry from the list only the ID from Sheet1 should be returned. The problem we are trying to solve is the following: We have a list of books that people can check out. On the checkout list we want to check out by book ID but because we do not know the ID's of the books by heart we want to have a single list that shows multiple columns like the Title and Author and when we select a book from the list only return the ID. I hope that I am expressing myself clear as English is not my primary language. "Roger Govier" wrote: Hi Gabriel on sheet2 in cell D2 enter =VLOOKUP($A2,Sheet1!$A:$D,2,0) This will return the Title, as it is in the second column of the range A:D on Sheet1 If you copy this across to columns E and F , changing the number to 2 and 3 respectively, you will get the information for Author and Owner You can make the number change automatically, by using the COLUMN() function. =COLUMN(B1) will return 2, COLUMN()C1) will return 3 etc. So Substituting this in the formula =VLOOKUP($A2,Sheet1!$A:$D,COLUMN(B1),0) means that you can just copy the formula across and it will automatically adjust for you. To prevent the formula returning errors when there is nothing in column A of sheet2, wrap it all in an IF statement =IF(A2="","",=VLOOKUP($A2,Sheet1!$A:$D,COLUMN(B1), 0)) Copy across through E2:F2 and copy D2:F2 down the sheet as far as required. -- Regards Roger Govier "Gabriel Lozano-Moran" wrote in message ... This is in Excel 2007. In my 1st worksheet I have a list of books with 4 columns: ID, Title, Authors, Owner On a 2nd worksheet I have the following columns: ID, Checked out to, Date I want in the lookup list for ID in my 2nd worksheet to show the Title, authors and owner columns but only return the ID of the book. I can create a lookup list that contains only one column. Any ideas or suggestions? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to create a multi column lookup list?
Hi Gabriel
I'm sorry, I misunderstood your request. Assuming your book ID's are 6 digits then You could make a concatenation of the values in Sheet1 Columns A:D in column E with the formula =TEXT(A2,"000000")&" , "&B2&" , "&C2&" , "&D2 Insert a new column A on Sheet 2 Set the data validation to be List with a source of =Sheet1!$E$2:$E$1000 (or whatever is a suitable range) In the new cell B2 (Your new ID column on Sheet2) use the formula =IF(A2="","",LEFT(A2,6)) Copy down as far as required. If the ID is different from my assumption, change the Text function accordingly in the concatenation, and change the number of characters in the LEFT function in cell B2 Alternatively, you could use a Combobox with some programming to achieve your needs. You will find good examples (and downloadable files) at Debra Dalgleish's site http://www.contextures.com/xlDataVal10.html -- Regards Roger Govier "Gabriel Lozano-Moran" wrote in message ... Hello Roger thanks for your answer but what we need is actually a single list with multiple colomns. In Sheet2!$A I want to use this multi column lookup list so when the list pops up you get to see the 3 columns and when you select an entry from the list only the ID from Sheet1 should be returned. The problem we are trying to solve is the following: We have a list of books that people can check out. On the checkout list we want to check out by book ID but because we do not know the ID's of the books by heart we want to have a single list that shows multiple columns like the Title and Author and when we select a book from the list only return the ID. I hope that I am expressing myself clear as English is not my primary language. "Roger Govier" wrote: Hi Gabriel on sheet2 in cell D2 enter =VLOOKUP($A2,Sheet1!$A:$D,2,0) This will return the Title, as it is in the second column of the range A:D on Sheet1 If you copy this across to columns E and F , changing the number to 2 and 3 respectively, you will get the information for Author and Owner You can make the number change automatically, by using the COLUMN() function. =COLUMN(B1) will return 2, COLUMN()C1) will return 3 etc. So Substituting this in the formula =VLOOKUP($A2,Sheet1!$A:$D,COLUMN(B1),0) means that you can just copy the formula across and it will automatically adjust for you. To prevent the formula returning errors when there is nothing in column A of sheet2, wrap it all in an IF statement =IF(A2="","",=VLOOKUP($A2,Sheet1!$A:$D,COLUMN(B1), 0)) Copy across through E2:F2 and copy D2:F2 down the sheet as far as required. -- Regards Roger Govier "Gabriel Lozano-Moran" wrote in message ... This is in Excel 2007. In my 1st worksheet I have a list of books with 4 columns: ID, Title, Authors, Owner On a 2nd worksheet I have the following columns: ID, Checked out to, Date I want in the lookup list for ID in my 2nd worksheet to show the Title, authors and owner columns but only return the ID of the book. I can create a lookup list that contains only one column. Any ideas or suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a column from e-mail TO: list | Excel Discussion (Misc queries) | |||
create a list dependant on info in a column | Excel Worksheet Functions | |||
match in multi-column and multi-row array | Excel Discussion (Misc queries) | |||
How do I create a list of items in a column? | Excel Worksheet Functions | |||
Multi-Column Drop-down list ? | Excel Discussion (Misc queries) |