Find 2nd instance of a best rate within a row

[vickylouiseuk]

Is there a way to look up the second occurrence of a value in a row?

https://drive.google.com/file/d/0B0392ULavezpbktIY29ST3kzWVU/view?usp=sharing

I've created an example on the link above - hopefully you can access it.

I have staff names across the column headings, and the data in the rows beneath. I need to be able to say, for each row, what the lowest rate is, along with the staff name. I've got it for the first one, and the formula will tell me the 2nd lowest rate, but I can't figure out how to match back the 2nd instance of the name.

G2 formula is =IFERROR(SMALL(IF($A2:$E2>0,$A2:$E2),1),"") (array)

I2 formula is =IFERROR(INDEX($A$1:$E$1,MATCH(G2,$A2:$E2,0)),"")

K2 formula is =IFERROR(SMALL(IF($A2:$E2>0,$A2:$E2),2),"") (array)

What should I put in M2 so that it comes up with Dave instead of Alex again??

 

[Marcelo Branco]

Welcome to Mr Excel

Try this array formula in M2 copied down
=IF(L2=1,"",INDEX($A$1:$E$1,SMALL(IF($A2:$E2=K2,COLUMN($A2:$E2)-COLUMN($A2)+1),2)))
Ctrl+Shift+Enter

Hope this helps

M.

 

[vickylouiseuk]

Welcome to Mr Excel

Try this array formula in M2 copied down
=IF(L2=1,"",INDEX($A$1:$E$1,SMALL(IF($A2:$E2=K2,COLUMN($A2:$E2)-COLUMN($A2)+1),2)))
Ctrl+Shift+Enter

Hope this helps

M.

Marcelo, thank you so much!
That worked, and I have managed to update it so that if it is just one instance it still returns the right name as follows
M4 =IF(L4=1,IFERROR(INDEX($A$1:$E$1,MATCH(K4,$A4:$E4,0)),""),INDEX($A$1:$E$1,SMALL(IF($A4:$E4=K4,COLUMN($A4:$E4)-COLUMN($A4)+1),2)))

Thanks again for such prompt assistance

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.