IF functions and Vlookups

Resolved · Urgent Priority · Version 365

Wed 15 Dec 2021, 12:39 Delegate Nicole said...

Nicole has attended:
Excel Intermediate course

IF functions and Vlookups

My formula is supposed to look up stock figures from two different things and add them together but it is just giving 0 if either of the stocks is 0.

for example one stock is 998 and the other is 0 so the formula gives 0 instead of 998.

the formula is below:

=IFERROR(VLOOKUP($B241,'PO Calcs Pivot'!$A:$V,7,0)+IFERROR(VLOOKUP($C241,'PO Calcs Pivot'!$A:$V,7,0),0),0)

The stock is pulling through on the pivot just not when the look up uses the above formula.

Can anyone please help?

Wed 15 Dec 2021, 15:55 Trainer Jens said...

RE: IF functions and Vlookups

Hi Nicole,

Thank you for the forum question.

Try:
=IFERROR(VLOOKUP($B241,'PO Calcs Pivot'!$A:$V,7,0),0)+IFERROR(VLOOKUP($C241,'PO Calcs Pivot'!$A:$V,7,0),0)

If it is not giving you the right result try:

=VLOOKUP($B241,'PO Calcs Pivot'!$A:$V,7,0)+VLOOKUP($C241,'PO Calcs Pivot'!$A:$V,7,0)

If this returns #NA error the Vlookups cannot find the lookup values and you will need to find out why. If the lookup value isn't 100% the same as found in the first column of the table array the Vlookup returns #NA

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 22 Dec 2021: Automatically marked as resolved.

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Excel tip:

Convert a column into row quickly in Excel 2010

Occasionally you might enter data into Excel vertically and then when you finish realize that actually it would look more clearer if it was represented in a horizontal format. If you follow these simple steps below, you can quickly change the data from going vertically to horizontally and vice versa.

First, select the column you want to convert into a row or a row into a column. Then right click and select Copy. Go to the sheet where you want to past this row as a column and select “Paste Special”. Remember to check the check-box “Transpose” and select “OK”.

View all Excel hints and tips