INDEX PROBLEM...I THINK

S

Steve

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
 
B

Bob Phillips

=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)
 
S

Steve

=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)








- Show quoted text -
Click to expand...

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
 
R

RagDyeR

Try this:

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

--

HTH,

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

=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)








- Show quoted text -
Click to expand...

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
 
S

Steve

Try this:

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

--

HTH,

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





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 -
Click to expand...

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
 
R

Ragdyer

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 !
---------------------------------------------------------------------------
Try this:

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

--

HTH,

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





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 -
Click to expand...

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
 
S

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 !








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 -
Click to expand...


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
 
R

Ragdyer

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








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 -
Click to expand...


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
 
S

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 !






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 -
Click to expand...

All of the data is returned from formulas...

Regards,

Steve
 
B

Bob Phillips

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

--
---
HTH

Bob

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



=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)








- Show quoted text -
Click to expand...

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
 
S

Steve

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

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


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("MiniC",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","ALCATEL-
LUCENT",IF(LEFT(D2,3)="NOK","NOKIA","")),"ALCATEL-
LUCENT")))),"ERICSSON")

In Cell H2:
=IF(LEFT(D2,8)="COMMUNIC","CCI",IF(ISERROR(FIND("KS",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(FIND("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:I2>0),F2:I2),"Mismatch")


Regards,

Steve
 
S

Steve

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

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","MOTOROLA",IF(LEFT(D2,3)="NOR","NORTEL",IF(ISERROR(FIND("Mi­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("KS",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(FIND("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:I2>0),F2:I2),"Mismatch")

Regards,

Steve
Click to expand...

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
 
R

Ragdyer

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 !
---------------------------------------------------------------------------

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

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","MOTOROLA",IF(LEFT(D2,3)="NOR","NORTEL",IF(ISERROR(FIND("Mi­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("KS",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(FIND("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:I2>0),F2:I2),"Mismatch")

Regards,

Steve
Click to expand...

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
 
S

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 !
---------------------------------------------------------------------------












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 -
Click to expand...

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,F2:J2,0),""),IF(LEN(F2:J2)>0,MATCH(F2:J2,F2:J2,0),""))>0,1))>1,"Mismatch",IF(ISERROR(INDEX(F2:J2,MATCH("?
*",F2:J2,0))),"",INDEX(F2:J2,MATCH("?*",F2:J2,0))))

Thanks for the help...

Regards,

Steve
 
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy & Paste Non Blanks Only 4
Multiple Scenarios 2
Sorting data with a formula 4
Finding Next Largest Number Based on Criteria 3
Index/Match problem 7
retrieve & count text 5
Multi column sumproduct 3
Index/match across multiple columns? 19

Top