Solved Excel Formula for identifyng when a , condition is match

October 6, 2013 at 23:29:26
Specs: Windows 7
Hi..!!
I have a large table with specific number for each entry, as follow:

Name Min Max TypeA TypeB TypeC TypeD TypeE
Joe 124 145 100 110 120 130 140
Mark 131 135 100 110 120 130 140
John 102 112 100 110 120 130 140

I need to identify the Types when are between each guy min and max; for example, Mark has TypeD because his Min and Max are between 130 and 140, John has TypeA and TypeB, etc.. etc.. I've tried with several if, or & and combinations; but fails. The formula has to shown what types are between each Min and Max. Can you give a hand??? Thanking you in advance for your time and help..!!!


See More: Excel Formula for identifyng when a , condition is match

Report •

✔ Best Answer
October 8, 2013 at 08:45:32
Another way to avoid duplicates with Mike's 2 cell suggestion would be to use the short version of the formula in Column J...

=INDIRECT(ADDRESS(1,MATCH(C2,$D2:$H2,1)+3,,)))

...and then use Conditional Formatting to set the font color to e.g. White if Column J = Column I.

In other words, the formula in Column J would return the duplicate, but the Conditional Formatting would hide it by matching the font color to the cell's fill color.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
October 7, 2013 at 04:03:49
First, a posting tip...

Please click on the blue line at the end of this post and read the instructions on how to post example data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 7, 2013 at 07:58:59
Assuming your values are in A1:H4, the first formula suggested below returns the following results:

Joe:        TypeC and TypeE
Mark:       TypeD and TypeD
John:       TypeA and TypeB

=IF(AND(B2>=D2,B2<=E2),$D$1,IF(AND(B2>=E2,B2<=F2),$E$1,
IF(AND(B2>=F2,B2<=G2),$F$1,IF(AND(B2>=G2,B2<=H2),$G$1,
IF(B2>=H2,$H$1)))))&" and " &IF(AND(C2>=D2,C2<=E2),$D$1,
IF(AND(C2>=E2,C2<=F2),$E$1,IF(AND(C2>=F2,C2<=G2),$F$1,
IF(AND(C2>=G2,C2<=H2),$G$1,IF(C2>=H2,$H$1)))))

If you want to eliminate "duplications" (e.g. TypeD and TypeD) try this formula. What it does is first check to see if the Min and Max values both return the same Type. If they do, then it just returns the Type for the min value.

=IF(IF(AND(B2>=D2,B2<=E2),$D$1,IF(AND(B2>=E2,B2<=F2),$E$1,
IF(AND(B2>=F2,B2<=G2),$F$1,IF(AND(B2>=G2,B2<=H2),$G$1,
IF(B2>=H2,$H$1)))))=IF(AND(C2>=D2,C2<=E2),$D$1,
IF(AND(C2>=E2,C2<=F2),$E$1,IF(AND(C2>=F2,C2<=G2),$F$1,
IF(AND(C2>=G2,C2<=H2),$G$1,IF(C2>=H2,$H$1))))),
IF(AND(B2>=D2,B2<=E2),$D$1,IF(AND(B2>=E2,B2<=F2),$E$1,
IF(AND(B2>=F2,B2<=G2),$F$1,IF(AND(B2>=G2,B2<=H2),$G$1,
IF(B2>=H2,$H$1))))), IF(AND(B2>=D2,B2<=E2),$D$1,
IF(AND(B2>=E2,B2<=F2),$E$1,IF(AND(B2>=F2,B2<=G2),$F$1,
IF(AND(B2>=G2,B2<=H2),$G$1,IF(B2>=H2,$H$1)))))&" and " &
IF(AND(C2>=D2,C2<=E2),$D$1,IF(AND(C2>=E2,C2<=F2),$E$1,
IF(AND(C2>=F2,C2<=G2),$F$1,IF(AND(C2>=G2,C2<=H2),$G$1,
IF(C2>=H2,$H$1))))))


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#3
October 7, 2013 at 14:03:07
Here are two shorter formulas
that you will need two cells for
but they seem to work:

With your data like:

     A     B     C       D      E      F      G      H
1) Name   Min   Max    TypeA  TypeB  TypeC  TypeD  TypeE
2) Joe    124   145    100    110    120    130    140
3) Mark   131   135    100    110    120    130    140
4) John   102   112    100    110    120    130    140


In cell I2 enter the formula:

=INDIRECT(ADDRESS(1,MATCH(B2,$D2:$H2,1)+3,1,TRUE))

In cell J2 enter the formula:

=IF(INDIRECT(ADDRESS(1,MATCH(B2,$D2:$H2,1)+3,1,TRUE))=INDIRECT(ADDRESS(1,MATCH(C2,$D2:$H2,1)+3,1,TRUE)),"",INDIRECT(ADDRESS(1,MATCH(C2,$D2:$H2,1)+3,1,TRUE)))

The second formula is so long because were checking
to make sure we don't have a double.

Just drag both formulas down as many rows as needed.

See how that works.

EDIT:

Same formulas only shortened a bit more:

=INDIRECT(ADDRESS(1,MATCH(B2,$D2:$H2,1)+3,,))

=IF(INDIRECT(ADDRESS(1,MATCH(B2,$D2:$H2,1)+3,,))=INDIRECT(ADDRESS(1,MATCH(C2,$D2:$H2,1)+3,,)),"",INDIRECT(ADDRESS(1,MATCH(C2,$D2:$H2,1)+3,,)))

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

Related Solutions

#4
October 8, 2013 at 08:45:32
✔ Best Answer
Another way to avoid duplicates with Mike's 2 cell suggestion would be to use the short version of the formula in Column J...

=INDIRECT(ADDRESS(1,MATCH(C2,$D2:$H2,1)+3,,)))

...and then use Conditional Formatting to set the font color to e.g. White if Column J = Column I.

In other words, the formula in Column J would return the duplicate, but the Conditional Formatting would hide it by matching the font color to the cell's fill color.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
October 11, 2013 at 13:43:40
Since we are using Two cells,
the second formula in cell J2 can be shortened even further with this:

=IF(INDIRECT(ADDRESS(1,MATCH(C2,$D2:$H2,1)+3,,))=I2,"",INDIRECT(ADDRESS(1,MATCH(C2,$D2:$H2,1)+3,,)))

Simply check to see if the answer for J2 equals the answer we already have for I2.

MIKE

http://www.skeptic.com/


Report •

Ask Question