#value error when using a sum function that includes vlookup cell references

Hi all

I'm using this formula:

=SUM(VLOOKUP((B82&C82),wkone,4,0),VLOOKUP((B82&C82),wktwo,4,0),VLOOKUP((B82&C82),wkthree,4,0),VLOOKUP((B82&C82),wkfour,4,0),VLOOKUP((B82&C82),wkfive,4,0))

The VLOOKUP function is working fine when there are numerical values in the cells I'm referencing but when there is text in any of the cells, I get a #VALUE error.

I've done a little research and apparently the SUM function is meant to help avoid those errors as opposed to using a simple arithmetic arguments (=a2+b2) but I can't find anything about the use of functions within the sum itself.

I need the formula to ignore text entries. How can I go about doing that?

Appreciate any help!

Cheers

Hi Pepe

In the tables that I'm referencing with the VLOOKUP(wkone, wktwo, etc, all on different sheets) I've concatenated the second and third columns in the table into the first column, then I do a vlookup for the concatenated value in the first column of the table, hence the use of '&'.

The VLOOKUP is working fine when I have numbers or empty cells so, despite not being entirely elegant or even the easiest way to do this, that isn't my issue. My issue is that when I have text in any of the cells I'm referencing, I get the #VALUE error so I need to find a way to add the numerical values and ignore the text.

I've attached the workbook. The errors are on the worksheet called 'Daily' and are highlighted in yellow to make it easier for you to find.

Thanks for your help, it's much appreciated!

Hi

I had some help and have changed my formula slightly and it works just fine:

=SUM(CHOOSE({1,2,3,4,5},VLOOKUP((B82&C82),wkone,4,0),VLOOKUP((B82&C82),wktwo,4,0),VLOOKUP((B82&C82),wkthree,4,0),VLOOKUP((B82&C82),wkfour,4,0),VLOOKUP((B82&C82),wkfive,4,0)))

Thanks for looking at it anyway, it's much appreciated!