Vlookup to Pivot Table, using Match function, returns error if can't find match value

Hi I'm having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them eg:

=SUM(VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("FAID",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("BPCM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMD",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE))

Where:
F13 = Employee number
Column C on the pivot 156 workbook is where the employee number is based.
The Match formula is then getting the column index from the column headings of the pivot tbale ie. "FAID"

This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.

Thanks in advance for any help you can give.

Andrew

Easiest way would be to use IFERROR()

So for each lookup, you would have IFERROR(VLOOKUP(......),0)

That way, if there is no match, and you get an error returned, it will revert to a 0 and not mess up the rest of the function

Brilliant thank you! So obvious when you know the answer!

Hi, I am using vlookup to get the value for my working, but don't know why some value is working fine & some value return as 0.

the formula using was =IFERROR((VLOOKUP(AB4,'Stock Rev 2'!$B:$Q,16,FALSE)), )
where, AB4 is a value in my pivot table; Stock Rev 2 is another worksheet in the same file.

Thanks in advance for your help.

Hi janewmc,

Welcome to the forum!

For the formula, if your VLOOKUP value can't be found, it reverts to the IFERROR value. So say your VLOOKUP is trying to find 'Item 1' in the list. If none of the cells in column B of your Stock Rev 2 sheet contain 'Item 1', then the VLOOKUP results in an error. This is where the IFERROR part comes in - instead of returning an error, you have left the return value blank - this is the , ) at the end of your formula. If you want it to return a blank cell instead of a 0, rather than just putting a space after the comma, your formula needs to be =IFERROR((VLOOKUP(AB4,'Stock Rev 2'!$B:$Q,16,FALSE)),""). Alternatively, you might want it to return a message; in this case, use the formula =IFERROR((VLOOKUP(AB4,'Stock Rev 2'!$B:$Q,16,FALSE)),"Value Not Found")

Hope this helps! If you'd like, you can upload your worksheet so I can have a more detailed look at it.

Hi,

Thanks for your reply.

The cells in column B of the Stock Rev 2 sheet contain 'Item 1', but still I can't look for the value I want.

Hmm... without seeing your workbook it's harder to pinpoint what's going on, any chance of you uploading it?