Finding the 2nd Occurance in rows (as opposed to columns)

[tasega]

Hi Everyone!


First, I think this site is amazing - thank you Chandoo and all the others who help to contribute!


I have attempted to solve this on my own, and after a few days, I am very frustrated with myself. I need to 1.) Find the 2nd occurance of a string in a row (I am stuck), and then 2.) Return the column header of the 2nd occurance using INDEX function.


Sample Workbook is here: http://hotfile.com/dl/121558211/975f87a/Example_Workbook.xls.html


Column H is where I need to obtain the results of the 2nd instance of "Tier 2" in the same row. I have tried using combinations of INDEX, Match and Small, but cannot get any to work. The only example I can find through browsing is a combination of Index, Small, and Row, which I also cannot get to work.


I would greatly appreciate any help! Thank you!

 

[Luke M]

Let's say your searching Row 2 for "Tasega", and the column headers are in Row 1.

=INDEX(1:1,SMALL(IF(A2:Z2="Tasega",COLUMN(A2:Z2)),2))


This is an array formula, use Ctrl+Shift+Enter, not just Enter to confirm.

Formula says "Using the index of row 1, take the 2nd smallest column number from A2:Z2 where "Tasega" is found"

 

[tasega]

So amazingly simple! It works perfect - thank you very very much Luke M!!!!!!

 

[Luke M]

You're welcome. =)