VLOOKUP Formula for Null Value

[MGC]

I have the following formula in a cell:

=VLOOKUP($E113,Eureka!$F$159:$V$182,6,0)+VLOOKUP($E113,Eureka!$F$106:$V$155,6,0)

If the first condition cannot be found then I would like Excel to look for
the second; if the second condition is not found then I would -0- entered in
the column.

However, if the first condition IS found but NOT the second then I would
like only the first value to be entered in the cell (this would also need to
work in the opposite direction...FIND the second but NOT the first).

Could someone please help me to set this up? This is a major spreadsheet
with numerous columns and lines.

 

[Pete_UK]

Try it like this:

=IF(ISNA(VLOOKUP($E113,Eureka!$F$159:$V$182,6,0)),
0,VLOOKUP($E113,Eureka!$F$159:$V$182,6,0))
+IF(ISNA(VLOOKUP($E113,Eureka!$F$106:$V$155­,6,0)),
0,VLOOKUP($E113,Eureka!$F$106:$V$155­,6,0))

Hope this helps.

Pete

 

[Bernard Liengme]

I did not have time to set up a test work sheet with the values you use but
here is my test formula
=IF(COUNTIF(Eureka!F1:F5,Sheet1!E3),VLOOKUP(Sheet1!E3,Eureka!F1:G5,2,FALSE),IF(COUNTIF(Eureka!F9:F13,Sheet1!E3),VLOOKUP(Sheet1!E3,Eureka!F9:G13,2,FALSE),"-0-"))

If E3 is found in F1:F5 of sheet Eureka then I get the value from the second
column of the range; if E3 is found in the second range (F9:F13) then I get
the value for its second column; otherwise I get "-0-". Your did not say
what was to happened if the E3 values was found in both ranges.
I think from my example you can make a few changes to match your situation
best wishes

 

[MGC]

Pete:

This worked great with one exception that I was not initially aware
of...sorry. In either array there may be 2 rows with the same lookup number.
The formula we used picks up the first line but not the second and I cannot
get around having two lines. Is there a way to get around this one?

 

[T. Valko]

Based on the formula you posted it looks like the value you're wanting to
return is numeric. Instead of using VLOOKUP try using SUMIF:

=SUMIF(Eureka!$F$159:$F$182,$E113,Eureka!$K$159:$K$182)+SUMIF(Eureka!$F$106:$F$155,$E113,Eureka!$K$106:$K$155)

 

[MGC]

This is perfect!! Thank you so much for your quick response! Have a Happy
and Safe Holiday!

 

[T. Valko]

You're welcome. Thanks for the feedback!