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.

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

On Dec 22, 1:53*am, MGC wrote:
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.

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.

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?

"MGC" wrote:

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.

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$1 06:$K$155)


--
Biff
Microsoft Excel MVP


"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.

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

"MGC" wrote:

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.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"MGC" wrote in message
...
This is perfect!! Thank you so much for your quick response! Have a
Happy
and Safe Holiday!

"MGC" wrote:

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.