Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    5

    Exclamation Unanswered: Index function on repeated value...!!

    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

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Steven,

    To do this in a single formula, you can use this array formula:
    Code:
    =INDEX(A1:A10,SMALL(IF((B1:B10="Vijay"),ROW(B1:B10)-ROW(B1)+1),2))
    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.
    Last edited by Colin Legg; 06-28-11 at 17:53.

  3. #3
    Join Date
    Jun 2011
    Posts
    5

    Thumbs up

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •