Vlookup error in some cells but not others

**penguintar** · 03-02-2011, 12:54 AM

Hi,

I am having trouble with an excel file that I have created to help me put together automated costings for my business. In a nutshell, I want the spreadsheet to automatically calculate the total weight of items based on the "type" (column C), the "material type/section type" (column D), the "dimensions" (columns F,G,H), "Quantity" (Column J), "Lengths" (Column K). When these columns are filled, it should automatically populate columns L, M, N.

Due to the nature of performing these calculations, I have used a mixture of nested IF functions, SUM functions, and Vlookup functions. All functions I have used appear correct but return only some of the correct values i.e. with some values the functions return #Value instead of the correct value. I believe this to be a problem caused by my vlookup functions which refer to 3 different sheets (within the same workbook) that contain the data lists. They search for a match (text description of item) within the relevant datasheet and then enter the number in the column next to the description.

I have tried all the usual procedures to solve this. I have added FALSE to the 4th argument, I have checked that the format for the data sheets is general, I have removed additional blank spaces, I have checked that the data range is absolute. I cannot seem to solve this problem.

Please see the file attached.

Any help would be greatly appreciated.

**brokenbiscuits** · 03-02-2011, 03:30 AM

Hi

The reason you're getting #VALUE errors is because some of the values in the sheet you're looking up aren't numbers as they have spaces before and/or after. Easiest way to fix this would be to put in another column in Col D, put in the formula =value(C1) and drag down, then copy > paste special (Values) over column C again. You can then delete column D.

**penguintar** · 03-02-2011, 05:23 AM

Thanks for the input, however this has not solved the problem. I have tried doing as you suggested but when for example I select "Section" in column C, and "PFC 230 x 90 x 32" in column D I get the same error #Value in columns M, N.

Perhaps there is a better way to write these equations. Currently they are as follows:

column M : =IF((C5="Plate"),(SUM(F5*G5*H5/1000000)*(VLOOKUP(D5,Plate!A3:B59,2,FALSE))),IF((C5="Tubular"),(SUM((F5-G5)*G5*L5)*(VLOOKUP(D5,Tubulars!A15:B86,2,FALSE))),IF((C5="Section"),(SUM(VLOOKUP(D5,Sections!B1:C17 83,2,FALSE))),0)))

Column N: =(IF((C7="Plate"),SUM(J7*M7/1000),IF((C7="Tubular"),SUM(L7*M7/1000),IF((C7="Section"),SUM(L7*M7/1000),0))))

Many thanks for your kind help.

**brokenbiscuits** · 03-02-2011, 06:52 AM

No, the VLOOKUP is working fine, the problem is that the VLOOKUP is bringing back a text string as some (most) of the values in column C on the Sections tab are not formatted as numbers as they have a space before and/or after the number.

Therefore when excel tries to use this text string in the SUM function it brings back a #VALUE error as you can't sum up text.

You either need to clean up your data (which I would recommend), or use the VALUE function in your formula (this should work, although I haven't tested it):

=IF((C5="Plate"),(SUM(F5*G5*H5/1000000)*(value(VLOOKUP(D5,Plate!A3:B59,2,FALSE)))),IF((C5="Tubular"),(SUM((F5-G5)*G5*L5)*(value(VLOOKUP(D5,Tubulars!A15:B86,2,FALSE)))),IF((C5="Section"),(SUM(value(VLOOKUP(D5,Se ctions!B1:C17 83,2,FALSE)))),0)))

**penguintar** · 03-03-2011, 02:34 AM

Many thanks, I have cleaned up the data lists and it seems to have corrected the problem. However I am still getting #N/A error when I refer to the other two sheets named "Tubulars" and "Plates". Again this seems to only occur with certain inputs in Column D of sheet "Costings". I cannot work out why this is happening as I have cleaned the data.

An example, when I select "Type" as "Plate" in column C, and select "Zirconium 702" in Column D. I get #NA in column.

A similar problem occurs for type="Tubular" with "Carbon Steel" in column D.

I have attached the updated file.

Any help much appreciated!

**brokenbiscuits** · 03-03-2011, 03:01 AM

The VLOOKUP is only looking at the range Plate!$A$3:B59 while Zirconium 702 is in row 73.

If you're going to keep adding data to the sheets and you're using 2007, it's probably best for you to use full column ranges - i.e. Plate!$A:$B, which will pick up any instances in the whole of column A.

**penguintar** · 03-03-2011, 03:09 AM

Perfect. Problem solved.

A thousand thank you's!

**pike** · 03-27-2011, 03:38 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save