Lookup

**epete367** · 12-13-2009, 10:39 AM

Is there a way to lookup data on one sheet - say find text "A" then have everything from column b, column c, column d - copied to ONE cell on another sheet?
Example: If A then copy from column b "1994", column c "Cabernet", column d "60.00" and past all of that into ONE cell on another sheet
I am trying to replicate my wine cellar.
Thanks

**zbor** · 12-13-2009, 11:00 AM

It'sw a combination on VLOOKUP and CONCATENATE function.

for detailed explanation please uplaod some example

**epete367** · 12-13-2009, 12:15 PM

Thank you for your prompt reply. You have headed me in the right direction. Do you also know if there is a better way to lookup? Currently I have a column where the rows are A1, A2, A3, B1,.... I use those just to reference the lookup. Then in the next column I have the actual row label the wine is racked in A1 - P21. On the next sheet (which is my cellar displayed) I have =LOOKUP(Sheet1!A2,Sheet1!B:B,Sheet1!C2)

**zbor** · 12-13-2009, 01:24 PM

Well, depending on your data you might also use combination of INDEX and MATCH function or VLOOKUP.

If you upload example we could be more precise with a answer

If you have some important data make example with dummy data but remain same structure of your workbook.

**epete367** · 12-13-2009, 01:32 PM

Sorry about posting second thread. I have attached a sample to show what I am trying to do. There are three sheets that are different views of the same Wine Cellar.
Currently, every time I move bottles I have to update all the sheets. I am trying to create one master sheet and then use formulas. That way when I change the location of a bottle it would update on the sheets automatically or if I remove a bottle. Right now it is a painstaking undertaking. There are more wines than are displayed on the attached sheets.
I use a cellar management program (MAC) but it doesn't track visually the actually cellar.
Again, sorry about the second post.

**epete367** · 12-13-2009, 01:34 PM

Attachment of wine files

**martindwilson** · 12-13-2009, 02:30 PM

ok for example in rack 1 wines c3
=VLOOKUP(C$2&$B3,Sheet1!$A$1:$I$1000,4,FALSE)&CHAR(10)&(VLOOKUP(C$2&$B3,Sheet1!$A$1:$I$1000,5,FALSE) ) drag across to col h then down to last row of bin 13
this would pull the name and grape from sheet 1 so in future as you update sheet 1 the associated cells would change.
the char(10) bit puts a line break in the cell
do the same for other sheets adding a different col in the vlookup as needed the above returns from cols 4 and 5 of the range a1:I1000 (hence the 4 and 5 in each vlookup)

**epete367** · 12-13-2009, 02:56 PM

Brilliant and I never could have done it! So do I even need the column A on "sheet one"? I was using it just as a reference for the lookup. Eventually as I add wine it may look like the attached sample sheet 1. In the sample Column A just will list A1 - A21, then B1 - B21, and so on replicating my cellar layout. Column B will have the actual location of the bottle.

**martindwilson** · 12-13-2009, 03:03 PM

you have to have some reference to look for but as long as you dont duplicate things! the look up will work fine on columb b instead,.donf forget if you remove col a column b will become col a, and youd have to adjust references. i'm not sure what you meaen tho if its in a1 its in a1 not b13 surely