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 140I 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..!!!

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

First, a posting tip... Please click on the

blue lineat 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.

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

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

message edited by mmcconaghy

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.

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

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History