Using an array within a formula

[vtentarelli]

I have a workbook containing about a dozen worksheets. Each worksheet contains data on various pieces of equipment. Equipment is sampled for microbial contamination. All data are entered on the spreadsheet titled, Main Page. Each sample is coded to identify the product, batch code, sampling device, piece of equipment, etc. When the sample data are entered onto the Main Page, the relevant information is copied onto the specific equipment spreadsheet. The data is then analyzed by year for average, and standard deviation for the purposes of calculating alert and action levels for the microbial load.

The formulas for capturing the data on each spreadsheet are quite cumbersome. The formulas list all the sampling points on the equipment. Some pieces of equipment have over 2 dozen sampling points. I would like to replace the formula listing all the sampling points with a reference to a table (or array) that is found on a separate spreadsheet called Codes." The formula is in column C titled TAPC in each spreadsheet. (TAPC refers to "Total Aerobic Plate Count) Below is an example of a formula used to capture the pertinent data from the Main Page:

=IF(OR(ISNUMBER(SEARCH("Line*",'Main Page'!N3)),'Main Page'!N3="Cord Reel Yank",'Main Page'!N3="Level Wind",'Main Page'!N3="Cord Sat. Under Control Panel",'Main Page'!N3="Cord Reel Yank",'Main Page'!N3="Cord Reel Wink",'Main Page'!N3="Cord Reel Vice",'Main Page'!N3="Cord Reel SLUD",'Main Page'!N3="Cord Reel RUMP",'Main Page'!N3="Cord Reel QUIP",'Main Page'!N3="Cord Reel PORK",'Main Page'!N3="Cord Reel PECK",'Main Page'!N3="Cord Reel NUTS",'Main Page'!N3="Cord Reel NICK",'Main Page'!N3="Cord Reel MUFF",'Main Page'!N3="Cord Reel LARD",'Main Page'!N3="Cord Reel KIWI",'Main Page'!N3="Cord Reel JANE",'Main Page'!N3="Cord Reel HUNT",'Main Page'!N3="Cord Reel HANK",'Main Page'!N3="Cord Reel GARP",'Main Page'!N3="Cord Reel FANG",'Main Page'!N3="Cord Reel ****",'Main Page'!N3="Cord Reel CLEM",'Main Page'!N3="Cord Reel CHAD",'Main Page'!N3="Cord Reel BUNT",'Main Page'!N3="Cord Reel BOOT",'Main Page'!N3="Cord Reel ZENO",'Main Page'!N3="Cord Reel HACK",'Main Page'!N3="Line Resistance Device 1",'Main Page'!N3="Toothbrush Line 1",'Main Page'!N3="Toothbrush Line 2",'Main Page'!N3="Line Resistance Device 2"),'Main Page'!D3,"")

As you can see it is quite cumbersome. I would like to replace it with a reference to an table in the spreadsheet Codes. But I can't quite wrap my head around the logic. I tried variations of the formula VLOOKUP, but kept getting error messages. Either I'm using the wrong formula or my syntax is faulty. My most recent effort was the following formula in C11:

=IF(VLOOKUP(Main Page N3,Codes H3:I57,2,0),Main Page D3,"").

I know that the VLOOKUP phrase should be equal to something, But the logic escapes me.

Pertinent spreadsheets are attached for reference. Many of the cells have error messages only because I had to delete several rows to cut down the size of the file. The formula I want changed is in Column C of each spreadsheet.