0

I'm trying to write an Excel formula that will look at 5 columns of data (for example, labeled SEL1, SEL2, SEL3, SEL4, and SEL5) and return the column name of the lowest column with a value above a 0.80.

For example,

| SEL1 | SEL2 | SEL3 | SEL4 | SEL5 |
|------+------+------+------+------|
| 0.68 | 0.81 | 0.84 | 0.92 | 0.95 |

In this case, I'd want it to identify SEL2.

So far I have =INDEX(A$1:E$1,MATCH(MIN(A2:E2),A2:E2,0)) - it's returning SEL 1 because it's only looking at the minimum, not the minimum above a 0.80

  • Gonna need a Match in there somewhere. What do you have already? – Raystafarian May 21 '16 at 19:51
4

Use the array formula:

=INDEX(A1:E1,MATCH(MIN(IF(A2:E2>0.8,A2:E2)),A2:E2))

enter image description here

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

  • @Ryan The little Min(If()) is really powerful!.........using it you can take a table of numbers, remove duplicates and sort the left-overs, all in one swell foop! – Gary's Student May 21 '16 at 20:42
  • Amazing, very good answer! – DankyNanky May 22 '16 at 10:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.