Hi,

I have a work sheet of 5 columns, in the 6 th column I want to look at
the other 5 columns (same row) and return information from the other 5
columns. There may be blanks in some of the adjacent cells in that
row. If any of the information that appears in the row more than
once, I want that value returned in the adjacent cell in the 6 th
column. If there is a mismatch of any data in those 5 adjacent cells,
I'd like to see "Mismatch" as a return in the adjacent cell in the 6
th column. See below:

apples apples apples apples
pears pears pears pears
apples apples pears Mismatch
oranges oranges oranges oranges oranges
peaches peaches peaches peaches peaches

=IF(COUNTA(A1:E1)=5,"",IF(SUMPRODUCT((A1:E1<"")/COUNTIF(A1:E1,A1:E1&""))1,"Mismatch",A1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Steve" wrote in message
oups.com...
Hi,

I have a work sheet of 5 columns, in the 6 th column I want to look at
the other 5 columns (same row) and return information from the other 5
columns. There may be blanks in some of the adjacent cells in that
row. If any of the information that appears in the row more than
once, I want that value returned in the adjacent cell in the 6 th
column. If there is a mismatch of any data in those 5 adjacent cells,
I'd like to see "Mismatch" as a return in the adjacent cell in the 6
th column. See below:

apples apples apples apples
pears pears pears pears
apples apples pears Mismatch
oranges oranges oranges oranges oranges
peaches peaches peaches peaches peaches

On Feb 19, 11:05 am, "Bob Phillips" wrote:
=IF(COUNTA(A1:E1)=5,"",IF(SUMPRODUCT((A1:E1<"")/COUNTIF(A1:E1,A1:E1&""))1*,"Mismatch",A1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Steve" wrote in message

oups.com...



Hi,

I have a work sheet of 5 columns, in the 6 th column I want to look at
the other 5 columns (same row) and return information from the other 5
columns. There may be blanks in some of the adjacent cells in that
row. If any of the information that appears in the row more than
once, I want that value returned in the adjacent cell in the 6 th
column. If there is a mismatch of any data in those 5 adjacent cells,
I'd like to see "Mismatch" as a return in the adjacent cell in the 6
th column. See below:

apples apples apples apples
pears pears pears pears
apples apples pears Mismatch
oranges oranges oranges oranges oranges
peaches peaches peaches peaches peaches- Hide quoted text -

- Show quoted text -

Hi,

Thanks for the quick response. However, when I have situation where
there is a blank in the first cell of that row and everything else is
a match, I get a "0" instead of the matching value. For example, in
the example above, if cell A1 were blank, I wouldn't get
"apples", ...the desired result...


Please advise,

Steve

Try this:

=IF(SUMPRODUCT((A1:E1<"")/COUNTIF(A1:E1,A1:E1&""))=1,LOOKUP(2,1/(A1:E10),A1:E1),"Mismatch")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Steve" wrote in message
oups.com...
On Feb 19, 11:05 am, "Bob Phillips" wrote:
=IF(COUNTA(A1:E1)=5,"",IF(SUMPRODUCT((A1:E1<"")/COUNTIF(A1:E1,A1:E1&""))1*,"Mismatch",A1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Steve" wrote in message

oups.com...



Hi,

I have a work sheet of 5 columns, in the 6 th column I want to look at
the other 5 columns (same row) and return information from the other 5
columns. There may be blanks in some of the adjacent cells in that
row. If any of the information that appears in the row more than
once, I want that value returned in the adjacent cell in the 6 th
column. If there is a mismatch of any data in those 5 adjacent cells,
I'd like to see "Mismatch" as a return in the adjacent cell in the 6
th column. See below:

apples apples apples apples
pears pears pears pears
apples apples pears Mismatch
oranges oranges oranges oranges oranges
peaches peaches peaches peaches peaches- Hide quoted text -

- Show quoted text -

Hi,

Thanks for the quick response. However, when I have situation where
there is a blank in the first cell of that row and everything else is
a match, I get a "0" instead of the matching value. For example, in
the example above, if cell A1 were blank, I wouldn't get
"apples", ...the desired result...


Please advise,

Steve

On Feb 19, 11:49 am, "RagDyeR" wrote:
Try this:

=IF(SUMPRODUCT((A1:E1<"")/COUNTIF(A1:E1,A1:E1&""))=1,LOOKUP(2,1/(A1:E10),*A1:E1),"Mismatch")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Steve" wrote in message

oups.com...
On Feb 19, 11:05 am, "Bob Phillips" wrote:





=IF(COUNTA(A1:E1)=5,"",IF(SUMPRODUCT((A1:E1<"")/COUNTIF(A1:E1,A1:E1&""))1**,"Mismatch",A1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Steve" wrote in message

roups.com...

Hi,

I have a work sheet of 5 columns, in the 6 th column I want to look at
the other 5 columns (same row) and return information from the other 5
columns. There may be blanks in some of the adjacent cells in that
row. If any of the information that appears in the row more than
once, I want that value returned in the adjacent cell in the 6 th
column. If there is a mismatch of any data in those 5 adjacent cells,
I'd like to see "Mismatch" as a return in the adjacent cell in the 6
th column. See below:

apples apples apples apples
pears pears pears pears
apples apples pears Mismatch
oranges oranges oranges oranges oranges
peaches peaches peaches peaches peaches- Hide quoted text -

- Show quoted text -

Hi,

Thanks for the quick response. However, when I have situation where
there is a blank in the first cell of that row and everything else is
a match, I get a "0" instead of the matching value. For example, in
the example above, if cell A1 were blank, I wouldn't get
"apples", ...the desired result...

Please advise,

Steve- Hide quoted text -

- Show quoted text -

Hi,

That did work better, but now I have those situations where there is
only one entry in the entire row. Where this is the case, I need that
entry to appear in the adjacent cell in 6 th column. I apologize for
my example not being that clear. I threw it together in a hurry this
morning without considering all of the possible scenerios.

Regards,

Steve

The formula I suggested will do that.

Have you tried it in that scenario?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Steve" wrote in message
oups.com...
On Feb 19, 11:49 am, "RagDyeR" wrote:
Try this:

=IF(SUMPRODUCT((A1:E1<"")/COUNTIF(A1:E1,A1:E1&""))=1,LOOKUP(2,1/(A1:E10),*A1:E1),"Mismatch")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Steve" wrote in message

oups.com...
On Feb 19, 11:05 am, "Bob Phillips" wrote:





=IF(COUNTA(A1:E1)=5,"",IF(SUMPRODUCT((A1:E1<"")/COUNTIF(A1:E1,A1:E1&""))1**,"Mismatch",A1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Steve" wrote in message

roups.com...

Hi,

I have a work sheet of 5 columns, in the 6 th column I want to look at
the other 5 columns (same row) and return information from the other 5
columns. There may be blanks in some of the adjacent cells in that
row. If any of the information that appears in the row more than
once, I want that value returned in the adjacent cell in the 6 th
column. If there is a mismatch of any data in those 5 adjacent cells,
I'd like to see "Mismatch" as a return in the adjacent cell in the 6
th column. See below:

apples apples apples apples
pears pears pears pears
apples apples pears Mismatch
oranges oranges oranges oranges oranges
peaches peaches peaches peaches peaches- Hide quoted text -

- Show quoted text -

Hi,

Thanks for the quick response. However, when I have situation where
there is a blank in the first cell of that row and everything else is
a match, I get a "0" instead of the matching value. For example, in
the example above, if cell A1 were blank, I wouldn't get
"apples", ...the desired result...

Please advise,

Steve- Hide quoted text -

- Show quoted text -

Hi,

That did work better, but now I have those situations where there is
only one entry in the entire row. Where this is the case, I need that
entry to appear in the adjacent cell in 6 th column. I apologize for
my example not being that clear. I threw it together in a hurry this
morning without considering all of the possible scenerios.

Regards,

Steve

On Feb 19, 2:37 pm, "Ragdyer" wrote:
The formula I suggested will do that.

Have you tried it in that scenario?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------"Steve" wrote in message

oups.com...
On Feb 19, 11:49 am, "RagDyeR" wrote:





Try this:

=IF(SUMPRODUCT((A1:E1<"")/COUNTIF(A1:E1,A1:E1&""))=1,LOOKUP(2,1/(A1:E10),**A1:E1),"Mismatch")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Steve" wrote in message

roups.com...
On Feb 19, 11:05 am, "Bob Phillips" wrote:

=IF(COUNTA(A1:E1)=5,"",IF(SUMPRODUCT((A1:E1<"")/COUNTIF(A1:E1,A1:E1&""))1***,"Mismatch",A1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Steve" wrote in message

roups.com...

Hi,

I have a work sheet of 5 columns, in the 6 th column I want to look at
the other 5 columns (same row) and return information from the other 5
columns. There may be blanks in some of the adjacent cells in that
row. If any of the information that appears in the row more than
once, I want that value returned in the adjacent cell in the 6 th
column. If there is a mismatch of any data in those 5 adjacent cells,
I'd like to see "Mismatch" as a return in the adjacent cell in the 6
th column. See below:

apples apples apples apples
pears pears pears pears
apples apples pears Mismatch
oranges oranges oranges oranges oranges
peaches peaches peaches peaches peaches- Hide quoted text -

- Show quoted text -

Hi,

Thanks for the quick response. However, when I have situation where
there is a blank in the first cell of that row and everything else is
a match, I get a "0" instead of the matching value. For example, in
the example above, if cell A1 were blank, I wouldn't get
"apples", ...the desired result...

Please advise,

Steve- Hide quoted text -

- Show quoted text -

Hi,

That did work better, but now I have those situations where there is
only one entry in the entire row. Where this is the case, I need that
entry to appear in the adjacent cell in 6 th column. I apologize for
my example not being that clear. I threw it together in a hurry this
morning without considering all of the possible scenerios.

Regards,

Steve- Hide quoted text -

- Show quoted text -


It seems to work when using the Fruits I have in my example, however
my spreadsheet list names of customers, like "ALCATEL-LUCENT",
"NOKIA", "NORTEL", "MOTOROLA", etc... For some reason it won't work
when I apply it to the customer names.....


Regards,

Steve

It sounds like your data is inconsistent, even though it may "look"
identical.

Are these names keyed in ... returns from formulas ... or imported from
other sources?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Steve" wrote in message
ups.com...
On Feb 19, 2:37 pm, "Ragdyer" wrote:
The formula I suggested will do that.

Have you tried it in that scenario?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------"Steve"
wrote in message

oups.com...
On Feb 19, 11:49 am, "RagDyeR" wrote:





Try this:

=IF(SUMPRODUCT((A1:E1<"")/COUNTIF(A1:E1,A1:E1&""))=1,LOOKUP(2,1/(A1:E10),**A1:E1),"Mismatch")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Steve" wrote in message

roups.com...
On Feb 19, 11:05 am, "Bob Phillips" wrote:

=IF(COUNTA(A1:E1)=5,"",IF(SUMPRODUCT((A1:E1<"")/COUNTIF(A1:E1,A1:E1&""))1***,"Mismatch",A1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Steve" wrote in message

roups.com...

Hi,

I have a work sheet of 5 columns, in the 6 th column I want to look
at
the other 5 columns (same row) and return information from the other
5
columns. There may be blanks in some of the adjacent cells in that
row. If any of the information that appears in the row more than
once, I want that value returned in the adjacent cell in the 6 th
column. If there is a mismatch of any data in those 5 adjacent
cells,
I'd like to see "Mismatch" as a return in the adjacent cell in the 6
th column. See below:

apples apples apples apples
pears pears pears pears
apples apples pears Mismatch
oranges oranges oranges oranges oranges
peaches peaches peaches peaches peaches- Hide quoted text -

- Show quoted text -

Hi,

Thanks for the quick response. However, when I have situation where
there is a blank in the first cell of that row and everything else is
a match, I get a "0" instead of the matching value. For example, in
the example above, if cell A1 were blank, I wouldn't get
"apples", ...the desired result...

Please advise,

Steve- Hide quoted text -

- Show quoted text -

Hi,

That did work better, but now I have those situations where there is
only one entry in the entire row. Where this is the case, I need that
entry to appear in the adjacent cell in 6 th column. I apologize for
my example not being that clear. I threw it together in a hurry this
morning without considering all of the possible scenerios.

Regards,

Steve- Hide quoted text -

- Show quoted text -


It seems to work when using the Fruits I have in my example, however
my spreadsheet list names of customers, like "ALCATEL-LUCENT",
"NOKIA", "NORTEL", "MOTOROLA", etc... For some reason it won't work
when I apply it to the customer names.....


Regards,

Steve

On Feb 19, 3:18 pm, "Ragdyer" wrote:
It sounds like your data is inconsistent, even though it may "look"
identical.

Are these names keyed in ... returns from formulas ... or imported from
other sources?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------"Steve" wrote in message

ups.com...
On Feb 19, 2:37 pm, "Ragdyer" wrote:





The formula I suggested will do that.

Have you tried it in that scenario?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------*"Steve"
wrote in message

roups.com...
On Feb 19, 11:49 am, "RagDyeR" wrote:

Try this:

=IF(SUMPRODUCT((A1:E1<"")/COUNTIF(A1:E1,A1:E1&""))=1,LOOKUP(2,1/(A1:E10),***A1:E1),"Mismatch")

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Steve" wrote in message

roups.com...
On Feb 19, 11:05 am, "Bob Phillips" wrote:

=IF(COUNTA(A1:E1)=5,"",IF(SUMPRODUCT((A1:E1<"")/COUNTIF(A1:E1,A1:E1&""))1****,"Mismatch",A1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Steve" wrote in message

roups.com...

Hi,

I have a work sheet of 5 columns, in the 6 th column I want to look
at
the other 5 columns (same row) and return information from the other
5
columns. There may be blanks in some of the adjacent cells in that
row. If any of the information that appears in the row more than
once, I want that value returned in the adjacent cell in the 6 th
column. If there is a mismatch of any data in those 5 adjacent
cells,
I'd like to see "Mismatch" as a return in the adjacent cell in the 6
th column. See below:

apples apples apples apples
pears pears pears pears
apples apples pears Mismatch
oranges oranges oranges oranges oranges
peaches peaches peaches peaches peaches- Hide quoted text -

- Show quoted text -

Hi,

Thanks for the quick response. However, when I have situation where
there is a blank in the first cell of that row and everything else is
a match, I get a "0" instead of the matching value. For example, in
the example above, if cell A1 were blank, I wouldn't get
"apples", ...the desired result...

Please advise,

Steve- Hide quoted text -

- Show quoted text -

Hi,

That did work better, but now I have those situations where there is
only one entry in the entire row. Where this is the case, I need that
entry to appear in the adjacent cell in 6 th column. I apologize for
my example not being that clear. I threw it together in a hurry this
morning without considering all of the possible scenerios.

Regards,

Steve- Hide quoted text -

- Show quoted text -

It seems to work when using the Fruits I have in my example, however
my spreadsheet list names of customers, like "ALCATEL-LUCENT",
"NOKIA", "NORTEL", "MOTOROLA", etc... For some reason it won't work
when I apply it to the customer names.....

Regards,

Steve- Hide quoted text -

- Show quoted text -

All of the data is returned from formulas...

Regards,

Steve

=IF(COUNTA(A6:E6)=0,"",IF(SUMPRODUCT((A6:E6<"")/COUNTIF(A6:E6,A6:E6&""))1,"Mismatch",LOOKUP(2,1/(A6:E60),A6:E6)))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Steve" wrote in message
oups.com...
On Feb 19, 11:05 am, "Bob Phillips" wrote:
=IF(COUNTA(A1:E1)=5,"",IF(SUMPRODUCT((A1:E1<"")/COUNTIF(A1:E1,A1:E1&""))1*,"Mismatch",A1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Steve" wrote in message

oups.com...



Hi,

I have a work sheet of 5 columns, in the 6 th column I want to look at
the other 5 columns (same row) and return information from the other 5
columns. There may be blanks in some of the adjacent cells in that
row. If any of the information that appears in the row more than
once, I want that value returned in the adjacent cell in the 6 th
column. If there is a mismatch of any data in those 5 adjacent cells,
I'd like to see "Mismatch" as a return in the adjacent cell in the 6
th column. See below:

apples apples apples apples
pears pears pears pears
apples apples pears Mismatch
oranges oranges oranges oranges oranges
peaches peaches peaches peaches peaches- Hide quoted text -

- Show quoted text -

Hi,

Thanks for the quick response. However, when I have situation where
there is a blank in the first cell of that row and everything else is
a match, I get a "0" instead of the matching value. For example, in
the example above, if cell A1 were blank, I wouldn't get
"apples", ...the desired result...


Please advise,

Steve

Well ... would you care to share the formulas?


--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

<snip

On Feb 19, 4:19 pm, "Ragdyer" wrote:
Well ... would you care to share the formulas?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

<snip

Not sure I understand what the formulas used to create the data will
do to help, but here they a


In Cell F2:

=IF(LEFT(D2,3)="LUC","ALCATEL-
LUCENT",IF(ISERROR(FIND("MdlrC",C2,1)),IF(ISERROR( FIND("OneBTS",C2,1)),IF(LEFT(D2,3)="MOT","MOTOROLA ",IF(LEFT(D2,3)="NOR","NORTEL",IF(ISERROR(FIND("Mi niC",C2,1)),IF(LEFT(E2,3)="NTN","NORTEL",""),"ALCA TEL-
LUCENT") )),"ALCATEL-
LUCENT"),IF(LEFT(D2,3)="NOR","NORTEL","")))

In Cell G2:
=IF(ISERROR(FIND("MCPLR-INFIN",C2,1)), IF(LEFT(D2,3)="ALC","ALCATEL-
LUCENT",IF(LEFT(D2,6)="HUGHES","HUGHES NETWORK
SYSTEMS",IF(LEFT(D2,6)="METAWA","METAWAVE
COMMUNICATIONS",IF(ISERROR(FIND("SERIES_II",C2,1)) ,IF(LEFT(E2,3)="300","ALCATEL-
LUCENT",IF(LEFT(D2,3)="NOK","NOKIA","")),"ALCATEL-
LUCENT")))),"ERICSSON")

In Cell H2:
=IF(LEFT(D2,8)="COMMUNIC","CCI",IF(ISERROR(FIND("K S",E2,1)),IF(LEFT(D2,6)="ANDREW","ALCATEL-
LUCENT",IF(LEFT(E2,3)="NOR","NORTEL",IF(LEFT(E2,3) ="910","MOTOROLA",IF(OR(LEFT(E2,3)="01D",LEFT(E2,3 )="016"),"MOTOROLA",IF(LEFT(E2,3)="018","MOTOROLA" ,""))))),"ALCATEL-
LUCENT"))

In Cell I2:
=IF(LEFT(E2,3)="408","ALCATEL-
LUCENT",IF(LEFT(E2,3)="918","MOTOROLA",IF(ISERROR( FIND("ERIC",D2,1)),IF(LEFT(E2,3)="201","ALCATEL-
LUCENT",IF(LEFT(E2,3)="920","ALCATEL-
LUCENT",IF(LEFT(E2,3)="NTU","NORTEL",IF(LEFT(E2,3) ="407","ALCATEL-
LUCENT",""))) ),"ERICSSON")))

In Cell J2:
=IF(LEFT(D2,3)="LUC","ALCATEL-
LUCENT",IF(ISERROR(FIND("NT",E2,1)),IF(ISERROR(FIN D("KS",E2,1)),
IF(ISERROR(FIND("MicrC",C2,1)),
IF(ISERROR(FIND("OneBTS",C2,1)),IF(LEFT(E2,3)="918 ","MOTOROLA",""),"ALCATEL-
LUCENT"),IF(OR(LEFT(E2,3)="407",LEFT(E2,3)="408"), "ALCATEL-
LUCENT","")),"ALCATEL-LUCENT"),"NORTEL"))

And the formula that I had used from the suggestions above has been re-
written to accommodate the data being in columns F thru J is:

=IF(SUMPRODUCT((F2:I2<"")/COUNTIF(F2:I2,F2:I2&""))=1,LOOKUP(2,1/
(F2:I20),F2:I2),"Mismatch")


Regards,

Steve

On Feb 19, 5:46 pm, "Steve" wrote:
On Feb 19, 4:19 pm, "Ragdyer" wrote:

Well ... would you care to share the formulas?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

<snip

Not sure I understand what the formulas used to create the data will
do to help, but here they a

In Cell F2:

=IF(LEFT(D2,3)="LUC","ALCATEL-
LUCENT",IF(ISERROR(FIND("MdlrC",C2,1)),IF(ISERROR( FIND("OneBTS",C2,1)),IF(L*EFT(D2,3)="MOT","MOTOROL A",IF(LEFT(D2,3)="NOR","NORTEL",IF(ISERROR(FIND("M i*niC",C2,1)),IF(LEFT(E2,3)="NTN","NORTEL",""),"AL CATEL-
LUCENT") )),"ALCATEL-
LUCENT"),IF(LEFT(D2,3)="NOR","NORTEL","")))

In Cell G2:
=IF(ISERROR(FIND("MCPLR-INFIN",C2,1)), IF(LEFT(D2,3)="ALC","ALCATEL-
LUCENT",IF(LEFT(D2,6)="HUGHES","HUGHES NETWORK
SYSTEMS",IF(LEFT(D2,6)="METAWA","METAWAVE
COMMUNICATIONS",IF(ISERROR(FIND("SERIES_II",C2,1)) ,IF(LEFT(E2,3)="300","ALC*ATEL-
LUCENT",IF(LEFT(D2,3)="NOK","NOKIA","")),"ALCATEL-
LUCENT")))),"ERICSSON")

In Cell H2:
=IF(LEFT(D2,8)="COMMUNIC","CCI",IF(ISERROR(FIND("K S",E2,1)),IF(LEFT(D2,6)="*ANDREW","ALCATEL-
LUCENT",IF(LEFT(E2,3)="NOR","NORTEL",IF(LEFT(E2,3) ="910","MOTOROLA",IF(OR(L*EFT(E2,3)="01D",LEFT(E2, 3)="016"),"MOTOROLA",IF(LEFT(E2,3)="018","MOTOROLA "*,""))))),"ALCATEL-
LUCENT"))

In Cell I2:
=IF(LEFT(E2,3)="408","ALCATEL-
LUCENT",IF(LEFT(E2,3)="918","MOTOROLA",IF(ISERROR( FIND("ERIC",D2,1)),IF(LEF*T(E2,3)="201","ALCATEL-
LUCENT",IF(LEFT(E2,3)="920","ALCATEL-
LUCENT",IF(LEFT(E2,3)="NTU","NORTEL",IF(LEFT(E2,3) ="407","ALCATEL-
LUCENT",""))) ),"ERICSSON")))

In Cell J2:
=IF(LEFT(D2,3)="LUC","ALCATEL-
LUCENT",IF(ISERROR(FIND("NT",E2,1)),IF(ISERROR(FIN D("KS",E2,1)),
IF(ISERROR(FIND("MicrC",C2,1)),
IF(ISERROR(FIND("OneBTS",C2,1)),IF(LEFT(E2,3)="918 ","MOTOROLA",""),"ALCATEL*-
LUCENT"),IF(OR(LEFT(E2,3)="407",LEFT(E2,3)="408"), "ALCATEL-
LUCENT","")),"ALCATEL-LUCENT"),"NORTEL"))

And the formula that I had used from the suggestions above has been re-
written to accommodate the data being in columns F thru J is:

=IF(SUMPRODUCT((F2:I2<"")/COUNTIF(F2:I2,F2:I2&""))=1,LOOKUP(2,1/
(F2:I20),F2:I2),"Mismatch")

Regards,

Steve

I think it has something to do with the fact that all of my data is
the
result of formulas and actual values. If I remove one of the formulas
in the very left hand column and the value I want as a result is in
the
second column, then the correct response will appear.


Regards,

Steve

See if this works:

=IF(SUMPRODUCT((F2:I2<"")/COUNTIF(F2:I2,F2:I2&""))=1,LOOKUP(2,1/(1-ISBLANK(F2:J2)),F2:J2),"Mismatch")
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Steve" wrote in message
oups.com...
On Feb 19, 5:46 pm, "Steve" wrote:
On Feb 19, 4:19 pm, "Ragdyer" wrote:

Well ... would you care to share the formulas?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------

<snip

Not sure I understand what the formulas used to create the data will
do to help, but here they a

In Cell F2:

=IF(LEFT(D2,3)="LUC","ALCATEL-
LUCENT",IF(ISERROR(FIND("MdlrC",C2,1)),IF(ISERROR( FIND("OneBTS",C2,1)),IF(L*EFT(D2,3)="MOT","MOTOROL A",IF(LEFT(D2,3)="NOR","NORTEL",IF(ISERROR(FIND("M i*niC",C2,1)),IF(LEFT(E2,3)="NTN","NORTEL",""),"AL CATEL-
LUCENT") )),"ALCATEL-
LUCENT"),IF(LEFT(D2,3)="NOR","NORTEL","")))

In Cell G2:
=IF(ISERROR(FIND("MCPLR-INFIN",C2,1)), IF(LEFT(D2,3)="ALC","ALCATEL-
LUCENT",IF(LEFT(D2,6)="HUGHES","HUGHES NETWORK
SYSTEMS",IF(LEFT(D2,6)="METAWA","METAWAVE
COMMUNICATIONS",IF(ISERROR(FIND("SERIES_II",C2,1)) ,IF(LEFT(E2,3)="300","ALC*ATEL-
LUCENT",IF(LEFT(D2,3)="NOK","NOKIA","")),"ALCATEL-
LUCENT")))),"ERICSSON")

In Cell H2:
=IF(LEFT(D2,8)="COMMUNIC","CCI",IF(ISERROR(FIND("K S",E2,1)),IF(LEFT(D2,6)="*ANDREW","ALCATEL-
LUCENT",IF(LEFT(E2,3)="NOR","NORTEL",IF(LEFT(E2,3) ="910","MOTOROLA",IF(OR(L*EFT(E2,3)="01D",LEFT(E2, 3)="016"),"MOTOROLA",IF(LEFT(E2,3)="018","MOTOROLA "*,""))))),"ALCATEL-
LUCENT"))

In Cell I2:
=IF(LEFT(E2,3)="408","ALCATEL-
LUCENT",IF(LEFT(E2,3)="918","MOTOROLA",IF(ISERROR( FIND("ERIC",D2,1)),IF(LEF*T(E2,3)="201","ALCATEL-
LUCENT",IF(LEFT(E2,3)="920","ALCATEL-
LUCENT",IF(LEFT(E2,3)="NTU","NORTEL",IF(LEFT(E2,3) ="407","ALCATEL-
LUCENT",""))) ),"ERICSSON")))

In Cell J2:
=IF(LEFT(D2,3)="LUC","ALCATEL-
LUCENT",IF(ISERROR(FIND("NT",E2,1)),IF(ISERROR(FIN D("KS",E2,1)),
IF(ISERROR(FIND("MicrC",C2,1)),
IF(ISERROR(FIND("OneBTS",C2,1)),IF(LEFT(E2,3)="918 ","MOTOROLA",""),"ALCATEL*-
LUCENT"),IF(OR(LEFT(E2,3)="407",LEFT(E2,3)="408"), "ALCATEL-
LUCENT","")),"ALCATEL-LUCENT"),"NORTEL"))

And the formula that I had used from the suggestions above has been re-
written to accommodate the data being in columns F thru J is:

=IF(SUMPRODUCT((F2:I2<"")/COUNTIF(F2:I2,F2:I2&""))=1,LOOKUP(2,1/
(F2:I20),F2:I2),"Mismatch")

Regards,

Steve

I think it has something to do with the fact that all of my data is
the
result of formulas and actual values. If I remove one of the formulas
in the very left hand column and the value I want as a result is in
the
second column, then the correct response will appear.


Regards,

Steve

On Feb 19, 9:09 pm, "Ragdyer" wrote:
See if this works:

=IF(SUMPRODUCT((F2:I2<"")/COUNTIF(F2:I2,F2:I2&""))=1,LOOKUP(2,1/(1-ISBLANK*(F2:J2)),F2:J2),"Mismatch")
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Steve" wrote in message

oups.com...
On Feb 19, 5:46 pm, "Steve" wrote:





On Feb 19, 4:19 pm, "Ragdyer" wrote:

Well ... would you care to share the formulas?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------

<snip

Not sure I understand what the formulas used to create the data will
do to help, but here they a

In Cell F2:

=IF(LEFT(D2,3)="LUC","ALCATEL-
LUCENT",IF(ISERROR(FIND("MdlrC",C2,1)),IF(ISERROR( FIND("OneBTS",C2,1)),IF(L**EFT(D2,3)="MOT","MOTORO LA",IF(LEFT(D2,3)="NOR","NORTEL",IF(ISERROR(FIND(" M*i*niC",C2,1)),IF(LEFT(E2,3)="NTN","NORTEL","")," ALCATEL-
LUCENT") )),"ALCATEL-
LUCENT"),IF(LEFT(D2,3)="NOR","NORTEL","")))

In Cell G2:
=IF(ISERROR(FIND("MCPLR-INFIN",C2,1)), IF(LEFT(D2,3)="ALC","ALCATEL-
LUCENT",IF(LEFT(D2,6)="HUGHES","HUGHES NETWORK
SYSTEMS",IF(LEFT(D2,6)="METAWA","METAWAVE
COMMUNICATIONS",IF(ISERROR(FIND("SERIES_II",C2,1)) ,IF(LEFT(E2,3)="300","ALC**ATEL-
LUCENT",IF(LEFT(D2,3)="NOK","NOKIA","")),"ALCATEL-
LUCENT")))),"ERICSSON")

In Cell H2:
=IF(LEFT(D2,8)="COMMUNIC","CCI",IF(ISERROR(FIND("K S",E2,1)),IF(LEFT(D2,6)="**ANDREW","ALCATEL-
LUCENT",IF(LEFT(E2,3)="NOR","NORTEL",IF(LEFT(E2,3) ="910","MOTOROLA",IF(OR(L**EFT(E2,3)="01D",LEFT(E2 ,3)="016"),"MOTOROLA",IF(LEFT(E2,3)="018","MOTOROL A*"*,""))))),"ALCATEL-
LUCENT"))

In Cell I2:
=IF(LEFT(E2,3)="408","ALCATEL-
LUCENT",IF(LEFT(E2,3)="918","MOTOROLA",IF(ISERROR( FIND("ERIC",D2,1)),IF(LEF**T(E2,3)="201","ALCATEL-
LUCENT",IF(LEFT(E2,3)="920","ALCATEL-
LUCENT",IF(LEFT(E2,3)="NTU","NORTEL",IF(LEFT(E2,3) ="407","ALCATEL-
LUCENT",""))) ),"ERICSSON")))

In Cell J2:
=IF(LEFT(D2,3)="LUC","ALCATEL-
LUCENT",IF(ISERROR(FIND("NT",E2,1)),IF(ISERROR(FIN D("KS",E2,1)),
IF(ISERROR(FIND("MicrC",C2,1)),
IF(ISERROR(FIND("OneBTS",C2,1)),IF(LEFT(E2,3)="918 ","MOTOROLA",""),"ALCATEL**-
LUCENT"),IF(OR(LEFT(E2,3)="407",LEFT(E2,3)="408"), "ALCATEL-
LUCENT","")),"ALCATEL-LUCENT"),"NORTEL"))

And the formula that I had used from the suggestions above has been re-
written to accommodate the data being in columns F thru J is:

=IF(SUMPRODUCT((F2:I2<"")/COUNTIF(F2:I2,F2:I2&""))=1,LOOKUP(2,1/
(F2:I20),F2:I2),"Mismatch")

Regards,

Steve

I think it has something to do with the fact that all of my data is
the
result of formulas and actual values. If I remove one of the formulas
in the very left hand column and the value I want as a result is in
the
second column, then the correct response will appear.

Regards,

Steve- Hide quoted text -

- Show quoted text -

Still couldn't make it work, however, I ended up using the following
and it worked out great..

=IF(SUM(IF(FREQUENCY(IF(LEN(F2:J2)0,MATCH(F2:J2,F 2:J2,0),""),IF(LEN(F2:J2)0,MATCH(F2:J2,F2:J2,0)," "))0,1))1,"Mismatch",IF(ISERROR(INDEX(F2:J2,MATC H("?
*",F2:J2,0))),"",INDEX(F2:J2,MATCH("?*",F2:J2,0))) )

Thanks for the help...

Regards,

Steve

Hi Steve,

I know I am in late on the post but here is a shorter approach which seems
to do what you want:

=IF(COUNTIF(A1:E1,LOOKUP("zz",A1:E1))=COUNTA(A1:E1 ),LOOKUP("zz",A1:F1),"Mismatch")

--
Cheers,
Shane Devenshire


"Steve" wrote:

Hi,

I have a work sheet of 5 columns, in the 6 th column I want to look at
the other 5 columns (same row) and return information from the other 5
columns. There may be blanks in some of the adjacent cells in that
row. If any of the information that appears in the row more than
once, I want that value returned in the adjacent cell in the 6 th
column. If there is a mismatch of any data in those 5 adjacent cells,
I'd like to see "Mismatch" as a return in the adjacent cell in the 6
th column. See below:

apples apples apples apples
pears pears pears pears
apples apples pears Mismatch
oranges oranges oranges oranges oranges
peaches peaches peaches peaches peaches