SOLVED

# Formula Working for Only Part of a Column in a Table

Occasional Contributor

# Formula Working for Only Part of a Column in a Table

I have a formula in a Table that works for rows 4-16, then I get a #VALUE! error.  I've looked at the input type and everything else I can think of but nothing works.

Has anyone else experienced this?  Any ideas?

10 Replies

# Re: Formula Working for Only Part of a Column in a Table

What is the formula?

# Re: Formula Working for Only Part of a Column in a Table

=IF(OR(G17=\$V\$4,G17=\$V\$7,G17=\$V\$14),N17*VLOOKUP(ecost[[#All],[Container]],ecost[#All],2,FALSE),L17*VLOOKUP(ecost[[#All],[Container]],ecost[#All],2,FALSE)) The column goes to row 797.

# Re: Formula Working for Only Part of a Column in a Table

Thanks. I fear I'd have to see the setup. Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

# Re: Formula Working for Only Part of a Column in a Table

Here's the file.  I hid the columns used in previous calculations but aren't referenced in this formula.

# Re: Formula Working for Only Part of a Column in a Table

I believe this is what you were going for:

``=LET(v,VLOOKUP([@Container],ecost7[#All],2,FALSE),IF(OR(G4=\$T\$4, G4=\$T\$7, G4=\$T\$14),N4*v,L4*v))``
best response confirmed by ToddMulder (Occasional Contributor)
Solution

# Re: Formula Working for Only Part of a Column in a Table

Or

=IF(OR([@Container]=\$T\$4, [@Container]=\$T\$7, [@Container]=\$T\$14),[@[Per Piece w/ Discount Plus Shipping]],[@[Per Flat w/ Discount Plus Shipping]])*VLOOKUP([@Container],ecost7[[Container]:[Ecost Factor]],2,FALSE)

# Re: Formula Working for Only Part of a Column in a Table

I tried this but am now getting a #NAME? error.

# Re: Formula Working for Only Part of a Column in a Table

This worked! Thank you!

# Re: Formula Working for Only Part of a Column in a Table

I spoke too soon. It worked on the test worksheet I attached but when I type it into the full spreadsheet I get the same error. Is it possible other data in the spreadsheet, even if it's not used in the formula, can affect this?

# Re: Formula Working for Only Part of a Column in a Table

My apologies. After a little more digging, I've got it. Thanks again!