I have a spreadsheet which I use for a BOM. It is linked to a Master table which is stored and updated as required. I used to use a drop down list which was fine until the content grew huge so it became easier to use a category list (C.SECTION, I.BEAM, U.BEAM etc) and the INDIRECT function on the next cell to get my category listing of sizes. It works perfect but then I want the value of the surface area and weight in my BOM which is easy if the listing is in 3 columns only using VLOOKUP function. Now I have 10 'named' columns for my drop down list and alongside these columns are the respective surface area and weight data. I do not want duplicate data in my master table which is what I have now. Now I have a problem. I cannot have the three columns in each category referred to in the name because the SA & Weight values get listed so no 3 column range to do a basic lookup and get adjacent values. In the row for my data the first cell D1 is a drop down list of the 'names' ANGLE C.SECTION FLAT GRATING H.BEAM I.BEAM PFC PIPE PLATE ROUND W.SECTION in the second cell E1 is the validation =INDIRECT(D1) which gives me my category size list. L- 25 x 25 x 3 L- 30 x 30 x 3 L- 40 x 40 x 3 L- 40 x 40 x 5 L- 45 x 45 x 4 L- 45 x 45 x 5 L- 50 x 50 x 4 L- 50 x 50 x 5 L- 50 x 50 x 6 L- 60 x 60 x 4 =VLOOKUP(E1,Table!$A$1:$C$700,2,FALSE))*(B1*(F1/1000))*C1 provides me with the original area calculation but is reliant on an array which I no longer have. Can anyone advise on how I can use the value in E1 to step from the single range name to offset sideways to the columns containing the other values I require. I can supply the xl workbooks if anyone needs to view if my explanation is not clear. Thanks in anticipation of a solution and apologies for long post.

I'm not going to claim to understand your question completely, but I'll throw this out: Can you name the ranges that contain the info for each item, basing the range name on the item? Let me explain... - Select the range that contains the data for Angle - Name this range AngleData - In similiar fashion, name the other ranges FlatData, PipeData, etc.) - Use VLOOKUP as follows: =VLOOKUP(E1,INDIRECT(E1&"Data"),2,0) etc. The INDIRECT function will "build" the range name based on whatever is E1 and use the Named Range AngleData, FlatData, etc as the table array. Does that help?

Amazing, it worked perfectly. Thankyou very much not only for the solution but the additional knowledge. I only made one mistake in that I named the data columns only as ANGLEDATA and forgot to include the ANGLE column. I found out by running the formula evaluator and spotted the step error. This solution has reduced the frustration level in the office using this sheet exponentially.

re: Amazing, it worked perfectly Amazing? What? You doubted it would work? I'm hurt! ;)

I am not amazed because of doubting you. I am amazed that with your kind assistance and my limited skills the formula slotted into my nested formula and worked exactly as I wanted. I have another query but I think for the benefit of the forum users I post as a separate query rather than add here. It will be posted soon.