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

[Rusty315]

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))

I get a #VALUE error where any value returned by one of the VLOOKUPs is text. 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

 

[sky1in5]

try to change your & to :

 

[sky1in5]

and your B:C only got 2 column, how come you can set to 4?

 

[sky1in5]

i suggest you upload your sample and let us know what the result you want.

 

[Rusty315]

Hi Skyin4

I'm using & instead of : because in the tables that I'm referencing (wkone, wktwo, etc) I've concatenated columns B and C in column A, then I do a vlookup for the concatenated value which is represented as (for example) 'B2&C2'. I'm looking in column 4 because there are a total of 8 columns in the table that I'm using and I need a sum of the values in the fourth column of each of the tables.

The VLOOKUP is working fine when I have numbers or empty cells so that isn't the issue. My issue is that when I have text in any of the cells I'm looking up, I get the #VALUE error so I need to find a way to add the numerical values and ignore the text.

I'm not sure how to go about posting up a copy of the workbook but I'm happy to do that.

Thanks for your help!

 

[sky1in5]

i see, hopefully someone can help you.

 

[Rusty315]

Thanks for trying though, I appreciate it!

 

[sky1in5]

you can try =isnumber

 

[Aladin Akyurek]

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))

I get a #VALUE error where any value returned by one of the VLOOKUPs is text. 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

=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)))

This does not handle the #N/A error if that also occurs.

 

[Rusty315]

Aladin, that is perfect!! Works perfectly. Thanks so much for your help, it's much appreciated!