Vlookup value is returning N/A when referencing a cell with sum formula in

[matthewoaks]

Hi All,
I have a simple lookup formula; in cell N7 I have the below

=IFNA(VLOOKUP(C7,'1SV'!A1:B731,2,FALSE,"")

The user manually enters a value in C7 and N7 returns the correct value

I now need to have a formula in C7
=SUM(A7+B7+D7) as an example and it still return the value like before in N7 but it's not working.
The cell returns N/A

Am I missing something in my vlookup formula? Both table and lookup cell is formatted to number at 3dp as it's showing the volume of a tank in m3

Any advice would be appreciated

 

[lrobbo314]

Try changing =IFNA(VLOOKUP(C7,'1SV'!A1:B731,2,FALSE,"") to =IFNA(VLOOKUP(C7,'1SV'!A1:B731,2,FALSE),"")

and =SUM(A7+B7+D7) to =SUM(A7,B7,D7)

 

[matthewoaks]

Hi Irobbo314
I've changed the sum formula as suggested, this still returns N/A in the cell with the lookup in

 

[RoryA]

I suspect you're getting rounding errors - are you trying to lookup a decimal value?

 

[Peter_SSs]

I now need to have a formula in C7
=SUM(A7+B7+D7) as an example and it still return the value like before in N7 but it's not working.
The cell returns N/A

Am I missing something in my vlookup formula? Both table and lookup cell is formatted to number at 3dp

What happens if you change that C7 formula to this?
=ROUND(A7+B7+D7,3)

 

[matthewoaks]

RoryA > I have to return the value to 3 decimal places

Peter > Thankers for your suggestion, tried that formula instead of SUM and still returns N/A result

Example of values;

(A) (B) (C) (D) (E)
observed ullage Trim Factor List Factor Corrected Ullage Tank Volume
650mm 8mm 40mm A+B+C = 690mm Lookup (D4) '690' in table and should return 364.085

 

[RoryA]

Are the values in column A of 1SV sheet formulas or literal values?

 

[matthewoaks]

Hi Rory, when I amended the post it reformatted without the spaces I put in; If you use A, B C etc as column ref in this example; this is a simplified version of my layout
(A) Obs ullage = 650
(B) Trim = 8
(C) List = 40
(D) Corrected Ullage = 698
(E) Cell with VLookup for value 698 from table

Sheet 1S-V (Sheet with Lookup table on)
Value 698 would be in column A
The tank volume is in column B (so cell E in this example should return 364.085)

 

[RoryA]

If A B and C are whole numbers, there shouldn't be an issue, unless perhaps the 698 in column A of 1S-V sheet is actually stored as text.

 

[matthewoaks]

Values in A are whole numbers
I've just increased the DP on the other cells to check and B, C and D are being rounded down to the nearest whole number