# 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 TypeEJoe 124 145 100 110 120 130 140Mark 131 135 100 110 120 130 140John 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..!!! See More: Excel Formula for identifyng when a , condition is match 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.

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

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))))))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,,)))MIKEmessage edited by mmcconaghy

Report •

Related Solutions

#4 October 8, 2013 at 08:45:32 October 11, 2013 at 13:43:40 