computing
  • 0

Solved Excel Formula for identifyng when a , condition is match

  • 0

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

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.

Share

1 Answer

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

    • 0