Lookup, Index, and/or match?

**rziegler** · 08-28-2008, 12:40 PM

I have another project that I have to do with the Bill of Materials ("BOM") files I posted about a few months ago. Last time it was updating or pulling information out of the existing BOM files; this time it's creating and putting information into a template and saving it as a new file. This process will need to repeat about 950 times (creating 950 new files from a template file filled from various lines a workbook with multiple tabs).

With me so far? See the ABRIDGED file that I've attached...

I have 4 tabs in a spreadsheet with different kinds of information:

Tab 1) About 10,000 rows of component (line) items that need to get entered into a template file. The format is 1 to 20 lines of COMPONENTS per item code. Each row has a "key" that is the item code. (NOTE: Multiple rows with the same item code)

Tab 2) About 1800 rows of times & processes with a "key" of the item code again. (NOTE: Multiple rows with the same item code)

Tab 3) About 3000 rows of messages with a "key" linking to the item code. (NOTE: Multiple rows with the same item code)

Tab 4) Exactly 948 rows of each unique item code (key) with a relative "save as" filename.

I need to pull ALL information for one item code from ALL tabs with the same item code, and get it into a template file... then save it as a new file.

I'm not even sure where to start, but my plan is to use the (tab 4) list of item codes and "save as" file names as the primary list that will reference the other tabs... please let me know your thoughts... anyone willing to help!

I'm a little overwhelmed with this one, and can hardly wrap my head around it. Everyone here at work is counting on me to figure this out. Any assistance will be much appreciated!

Thanks in advance!

**Macro-wave** · 08-28-2008, 03:44 PM

Since some of your item codes are the same, you can label them to make them temporarily different (i.e. concatenate consecutive numbers to each line with the same item code) to make the lookup function and index function locate unique items. Then, use the left, right or mid functions on the altered item codes to pull all the information together. You can think of the way your particular way of using this method, as there are many.

I wouldn't go with the match function. It's not one that I have ever found handy. To the peanut gallery:I'm aware there are ways to use it here.

At first glance, that's what I come up with because I do stuff like this all the time. I'll take some time to look a your spreadsheet in more detail and see if there is something that I may have missed.
Feel free to let me know if you need additional information.

**rziegler** · 08-28-2008, 04:12 PM

I'm relatively new to the formulas available in Excel.

Index allows me to "index" up to 256 rows, right? I have 10,000 rows.

Is lookup better, or is lookup used in conjunction with index?

**Cheeky Charlie** · 08-28-2008, 05:38 PM

Lookups are really handy formulae which deliver "equivalents" to values from (lookup) tables. So a lookup says "Look at this cell, compare it with this row or column of data, then tell me something from the same column or row as the same (or possibly next lowest) value in the lookup table. It's really best seen with an example, attached.

Match and Index are often used as a pair to "cross-reference" lookups as a matrix. This is ugly and hard to read and slow. Basically, this splits the logic of lookup into two components, it find the "address" of the item and then applies it to extract the result. The equivalent is shown with my example.

I think you will probably benefit from pivot tables in some form or other, but I can't look at this because it will take (about) forever to download a forwards copmatibility patch here. So I'll look again tomorrow, unless it's been solved by then...

For now, there's some background info for you.

HTH