I am using the following:

=VLOOKUP($A15,$A$81:$AB$124,Q$1,FALSE)+VLOOKUP($A1 5,$A$126:$AB$160,Q$1,FALSE)

Where $A15 may not exist in either of the arrays or only in one of the two.

How do I allow for this so I get a value rather than the #N/A?

Thanks in advance
--
Jeff C
Live Well .. Be Happy In All You Do

I'm not sure if this will work, but try it

=SUMPRODUCT(--(OFFSET(Sheet1!$A15,0,Sheet1!$Q$1,146,1)=$A$15),(O FFSET(Sheet1!$A15,0,Sheet1!$Q$1,146,1)))

HTH,
Barb Reinhardt



"Jeff C" wrote:

I am using the following:

=VLOOKUP($A15,$A$81:$AB$124,Q$1,FALSE)+VLOOKUP($A1 5,$A$126:$AB$160,Q$1,FALSE)

Where $A15 may not exist in either of the arrays or only in one of the two.

How do I allow for this so I get a value rather than the #N/A?

Thanks in advance
--
Jeff C
Live Well .. Be Happy In All You Do

You can check each portion to see if it returns an error first:

=if(isna(firstvlookup),0,firstvlookup)
+if(isna(2ndvlookup),0,2ndvlookup)

If you're using xl2007, you could look at =iferror() in excel's help. You won't
have to duplicate each vlookup formula.



Jeff C wrote:

I am using the following:

=VLOOKUP($A15,$A$81:$AB$124,Q$1,FALSE)+VLOOKUP($A1 5,$A$126:$AB$160,Q$1,FALSE)

Where $A15 may not exist in either of the arrays or only in one of the two.

How do I allow for this so I get a value rather than the #N/A?

Thanks in advance
--
Jeff C
Live Well .. Be Happy In All You Do

--

Dave Peterson

Thank you both, the ISNA function is what I used successfully, another lesson
learned!
--
Jeff C
Live Well .. Be Happy In All You Do


"Dave Peterson" wrote:

You can check each portion to see if it returns an error first:

=if(isna(firstvlookup),0,firstvlookup)
+if(isna(2ndvlookup),0,2ndvlookup)

If you're using xl2007, you could look at =iferror() in excel's help. You won't
have to duplicate each vlookup formula.



Jeff C wrote:

I am using the following:

=VLOOKUP($A15,$A$81:$AB$124,Q$1,FALSE)+VLOOKUP($A1 5,$A$126:$AB$160,Q$1,FALSE)

Where $A15 may not exist in either of the arrays or only in one of the two.

How do I allow for this so I get a value rather than the #N/A?

Thanks in advance
--
Jeff C
Live Well .. Be Happy In All You Do

--

Dave Peterson

Try this:

=SUMIF(A81:A124,A15,INDEX(A81:AB124,,Q1),0)+SUMIF( A126:A160,A15,INDEX(A126:AB160,,Q1),0)

--
Biff
Microsoft Excel MVP


"Jeff C" wrote in message
...
I am using the following:

=VLOOKUP($A15,$A$81:$AB$124,Q$1,FALSE)+VLOOKUP($A1 5,$A$126:$AB$160,Q$1,FALSE)

Where $A15 may not exist in either of the arrays or only in one of the
two.

How do I allow for this so I get a value rather than the #N/A?

Thanks in advance
--
Jeff C
Live Well .. Be Happy In All You Do

Ooops!

Typo:

=SUMIF(A81:A124,A15,INDEX(A81:AB124,,Q1),0)+SUMIF( A126:A160,A15,INDEX(A126:AB160,,Q1),0)

Should be:

=SUMIF(A81:A124,A15,INDEX(A81:AB124,,Q1))+SUMIF(A1 26:A160,A15,INDEX(A126:AB160,,Q1))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this:

=SUMIF(A81:A124,A15,INDEX(A81:AB124,,Q1),0)+SUMIF( A126:A160,A15,INDEX(A126:AB160,,Q1),0)

--
Biff
Microsoft Excel MVP


"Jeff C" wrote in message
...
I am using the following:

=VLOOKUP($A15,$A$81:$AB$124,Q$1,FALSE)+VLOOKUP($A1 5,$A$126:$AB$160,Q$1,FALSE)

Where $A15 may not exist in either of the arrays or only in one of the
two.

How do I allow for this so I get a value rather than the #N/A?

Thanks in advance
--
Jeff C
Live Well .. Be Happy In All You Do

Just a warning...

If the value in A15 occurs multiple times in A81:a124 or multiple times in
A126:A160, then this formula may not return what the original poster needs.




"T. Valko" wrote:

Ooops!

Typo:

=SUMIF(A81:A124,A15,INDEX(A81:AB124,,Q1),0)+SUMIF( A126:A160,A15,INDEX(A126:AB160,,Q1),0)

Should be:

=SUMIF(A81:A124,A15,INDEX(A81:AB124,,Q1))+SUMIF(A1 26:A160,A15,INDEX(A126:AB160,,Q1))

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message
...
Try this:

=SUMIF(A81:A124,A15,INDEX(A81:AB124,,Q1),0)+SUMIF( A126:A160,A15,INDEX(A126:AB160,,Q1),0)

--
Biff
Microsoft Excel MVP


"Jeff C" wrote in message
...
I am using the following:

=VLOOKUP($A15,$A$81:$AB$124,Q$1,FALSE)+VLOOKUP($A1 5,$A$126:$AB$160,Q$1,FALSE)

Where $A15 may not exist in either of the arrays or only in one of the
two.

How do I allow for this so I get a value rather than the #N/A?

Thanks in advance
--
Jeff C
Live Well .. Be Happy In All You Do



--

Dave Peterson