guys,

Suppose that i hav Column A and Column B like shown below (spaces separate two columns)...

ColumnA ColumnB

Swetha Vijay
Nikhi Madhu
Sumanth Vijay
Bittu Vijay
Sudhir Madhu
******* Sam
Rakhi Paul

I want to get the corresponding value of "Vijay" in column B for the second occurrence from column A (i.e I should get the value as "Sumanth").. Using the "INDEX" function am getting only the value of first occurrence (i.e Swetha) and am unable to get it for the second occurrence of "Vijay".. I tried a lot but I failed. Please help me with this.

Thanks in advance...
Steven

Hi Steven,

To do this in a single formula, you can use this array formula:
When you type it into the formula bar you must complete the entry with CTRL+SHIFT+ENTER, not just the usual ENTER. If done correctly then Excel will automatically surround the formula with curly braces { }.

A1:A10 is the the range containing the name you want to return.
B1:B10 is the range containing the name you want to look up against.
"Vijay" is the look up value.
B1 is the first cell in the look up range B1:B10
2 represents the second match.

Colin, u are awesome..!!
It worked man !! ho ho ho !!!
Thanks for that !!