INDEX PROBLEM...I THINK

  • Thread starter Thread starter Steve
  • Start date Start date
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
 
=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)
 
=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
 
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
 
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
 
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
 
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
 
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
 
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
 
=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
 
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
 
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
 
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
 
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
 
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
Sum & Lookup function 5
PLS HLP! Countifs Syntax Problem? 5
sumif 1
Count Vaules in a range that appear in another range 5
help with making a function to filter lists 2
Finding Next Largest Number Based on Criteria 3

Back
Top