How to create a multi column lookup list?

[Gabriel Lozano-Moran]

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?

 

[Roger Govier]

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

 

[Gabriel Lozano-Moran]

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]

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