Vlookup Formula Question

Care to post your formula along with info about what kind of data (numbers, text) the ranges in your data area houses?

This would be easier if I could send you a copy of the workbook in question. Then, we would exchange ideas either via email or an online chat or instant message program.

I use Yahoo! Instant Messenger and AOL Instant Messenger. Either one works for me.

LarrySC said:

This would be easier if I could send you a copy of the workbook in question. Then, we would exchange ideas either via email or an online chat or instant message program.

I use Yahoo! Instant Messenger and AOL Instant Messenger. Either one works for me.

Click to expand...

See,

http://216.92.17.166/board2/viewtopic.php?t=49751

in particular 6,8, and 9.

Brian from Maui said:

LarrySC said:

This would be easier if I could send you a copy of the workbook in question. Then, we would exchange ideas either via email or an online chat or instant message program.

I use Yahoo! Instant Messenger and AOL Instant Messenger. Either one works for me.

Click to expand...

See,

http://216.92.17.166/board2/viewtopic.php?t=49751

in particular 6,8, and 9.

Click to expand...

Sorry for the breach of protocol. I am new here and didn't realize the usual and accepted method of asking questions. Please forgive me!

Let me try to explain my situation.

I have a page in a workbook that I have 645 lines of data listed. There are seven columns in each record. They are labeled as such:

Column A: Shape Code
Column B: 1st Dimension
Column C: 2nd Dimension
Column D: 3rd Dimension
Column E: Unit of Measure
Column F: A formula that 'builds' a string of columns A, B, C, and D (depending on if there is a value in those columns)
Column G: A unit weight for each shape code

Now, on another page of the workbook, I have set up a 'bill of material' table for the user to enter items of each shop drawing. I want the spreadsheet to do a lookup on each line of material and find the unit weight, then calculate the total weight for that line item based on the size (area or length) and quantity. The columns on the BOM page are labeled like this:

Column A: Quantity
Column B: Ship Mark
Column C: Piece Mark
Column D: Shape Code
Column E: 1st Dimension
Column F: 2nd Dimension
Column G: 3rd Dimension
Column H: Feet
Column I: Inches
Column J: Fraction
Column K: Remarks
Column L: Unit Weight this is the cell that contains the vlookup formula: =IF($O4<>"",+$O4,IF(D4="","",VLOOKUP($P4,'WEIGHT TABLE'!$F$4:$G$648,2)))

Column M: Area/Length this is the cell that calculates the length of a piece or the area for plate shapes

Column N: Extended Weight this is the cell that multiplies the quantity, unit weight, and length/area

Column O: Certain steel shapes have the UNIT WEIGHT in the member size. This column determines if that statement is true.

Column P: Similar to COLUMN F on the WEIGHT TABLE sheet in that it 'builds' a string of information for the vlookup formula to work off of: =IF($G4="",IF($F4="",CONCATENATE($D4," ",$E4),CONCATENATE($D4," ",$E4," x ",$F4)),CONCATENATE($D4," ",$E4," x ",$F4," x ",$G4))

Column Q: Builds a length
Column R: " " " "
Column S: " " " "

Now, the vlookup formula that is in Column L works correctly on Rows 4 through Row 569. Here, the vlookup formula returns a value of "76.070". which is the correct value for Row 626. This value of "76.070" continues to show up through Row 591. Then, the vlookup formula begins to return a value of "1.2730", which is the correct value for Row 469!

I cannot figure out why the vlookup formula is returning these bogus numbers on Rows 569 through 626.

Is the value derived from the Concatenate formula unique? And is the derived value an exact match somewhere in column F of your lookup table? If so, add False (0) to your 4th argument of your Vlookup formula

IF($O4,+$O4,IF(D4="","",VLOOKUP($P4,'WEIGHT TABLE'!$F$4:$G$648,2,0))).

If that doesn't work can you sort column F?

And if that doesn't work, post some sample data as Aladin has asked.

Brian from Maui said:

Is the value derived from the Concatenate formula unique? And is the derived value an exact match somewhere in column F of your lookup table? If so, add False (0) to your 4th argument of your Vlookup formula

IF($O4,+$O4,IF(D4="","",VLOOKUP($P4,'WEIGHT TABLE'!$F$4:$G$648,2,0))).

If that doesn't work can you sort column F?

And if that doesn't work, post some sample data as Aladin has asked.

Click to expand...

I added the False(0) argument as you suggested. This corrected the problem. Thanks. Can you give me a brief explanation as to why adding the False (0) argument worked? Thanks in advance.