Using Lookup in Excel 2010

Topic

New Reply

When I moved to Office 2010, I opted not to include Access because I had only one application: a database of my CD music collection. I imported by Access database file into Excel and except for losing the pretty data entry forms, the new “table” function of Excel works great for this fairly simple database. The new CD collection workbook consists of two worksheets: the first sheet (I’ll call it the “CD” sheet) contains many columns with all the pertinent information from each CD (title, performer, label, etc.). Most importantly for this discussion, each row on the “CD” sheet contains the unique catalog number (“primary key” in database speak) for that CD. The second sheet (I’ll refer to that as the “song” sheet) consists of two columns: one for all of the song titles from all the CDs and a second column with the catalog number of the CD on which that song resides. Note: this collection is primarily jazz and classical, so the same song title can occur on many different CDs.

Now that this database is in spreadsheet form, what I would like to do is add a third column on the “song” sheet which includes the actual CD title, not just the CD’s catalog number. I thought it would be easy to use Excel’s “Lookup” function to take the CD catalog number from the “song” sheet, cross-reference that number with the catalog numbers on the “CD” sheet, and then copy the CD title onto the “song” sheet. I’ve tried most every combination of arguments in the “Lookup” (and related) function and haven’t been able to automatically add the CD title associated with each song title, based on the unique catalog number.

Any ideas?

Reply | Quote

Replies

Hello – Try the attached – see Songs tab in Col C.

If not what is needed, Please attach the first 10 rows, or so, from each of your sheets.

Tim

Reply | Quote

Tim,

Thank you for the reply and my apologies for taking so long to acknowledge your input. I was just able to get back to working the issue and between your suggestion to use VLOOKUP and quite a bit of experimentation, I finally got the results I was seeking. The “secret” to success was moving the CD “Catalog Number” column (used as the primary key) to the first column on the primary sheet. Any other location and either the VLOOKUP function wouldn’t work at all, or it would return incorrect results. It apparently does not matter where the primary key column is on the “dependent” sheet, but apparently it must be the first column on the database sheet.

There may be a lookup function which does not require specific column placement of the data, but this works, so I’m happy. Thanks again for the response.

BK Howard

Reply | Quote

Hello BK Howard – That is great that you got it to work. Excel has numerous Database features. You will be pleasantly surprised with what Excel can do with databases.

You are correct that the primary key is in the first column of the lookup range.

and except for losing the pretty data entry forms, the new “table” function of Excel works great for this fairly simple database.

In Excel, try out Data>Form to do data entry.

You might also want to explore Data>Filter>AutoFilter and Data>Filter>Advanced Filter.
Another great feature of Excel is Data>Pivot Table.

Tim

Reply | Quote

Tim, Thank you for this tip:

In Excel, try out Data>Form to do data entry.

For those of you who have to play, “Where’s that command?” in Excel 2010, the “Form” function must be loaded as a custom ribbon item. The function is found at: File | Options | Customize Ribbon | All Commands| Form

Reply | Quote

If you do not want the Primary Key of the lookup range to be the first column then you can use MATCH and INDEX together

Reply | Quote

Andrew,

Appreciate this idea as well:

If you do not want the Primary Key of the lookup range to be the first column then you can use MATCH and INDEX together

I had looked at MATCH and INDEX in Microsoft Help as a possible solution, but their example made it less than clear how it was used. Your example was great and made it very clear how to setup that combo-function for my use. Using those functions together (now that I understand the syntax) will come in handy for future database manipulations. Thank you.

Reply | Quote