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

matthewoaks

New Member
Joined
Jan 31, 2014
Messages
30
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
 

Some videos you may like

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
Joined
Jul 14, 2008
Messages
2,758
Office Version
365, 2019, 2016
Platform
Windows
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
Joined
Jan 31, 2014
Messages
30
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
Joined
May 2, 2008
Messages
34,986
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
I suspect you're getting rounding errors - are you trying to lookup a decimal value?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,684
Office Version
365
Platform
Windows
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
Joined
Jan 31, 2014
Messages
30
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
Joined
May 2, 2008
Messages
34,986
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Are the values in column A of 1SV sheet formulas or literal values?
 

matthewoaks

New Member
Joined
Jan 31, 2014
Messages
30
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
Joined
May 2, 2008
Messages
34,986
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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
Joined
Jan 31, 2014
Messages
30
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,283
Messages
5,467,739
Members
406,549
Latest member
midcoastchris04

This Week's Hot Topics

Top