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

#### matthewoaks

##### New Member
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

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### lrobbo314

##### Well-known Member
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

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

#### RoryA

##### MrExcel MVP, Moderator
I suspect you're getting rounding errors - are you trying to lookup a decimal value?

#### Peter_SSs

##### MrExcel MVP, Moderator
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

##### New Member
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

Last edited:

#### RoryA

##### MrExcel MVP, Moderator
Are the values in column A of 1SV sheet formulas or literal values?

#### matthewoaks

##### New Member
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

##### MrExcel MVP, Moderator
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

##### New Member
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