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,FALS E),"-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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"MGC" wrote in message
...
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.