Return the value from the adjacent row for the nth occ

• Hi Robert Thanks once again for your courteous reply to my question posted in the thread Return the value from the adjacent column for the nth occurence.My qestion basically was how would I modify the formulae if the value to be looked up was listed in a column unlike in a row as ithe case stated in the example .Specially if the column to be looked up was in another sheet or anothr workbook.In most of the cases I would use vlookup but doing that would always return the first occurences for values repeating themselves. Please find attached a orkbook which will help you understand my Question better.I am using names of cricketers and cricket teams as I connect less with soccer!!!!Please refer sheet 1 and sheet 2 of the workbookw

Reference to the thread link - Return the value from the adjacent row for the nth occ

Files

• Re: Return the value from the adjacent row for the nth occ

Try:

=INDEX(Data!\$C\$3:\$C\$8,SMALL(IF(Data!\$B\$3:\$B\$8=A2,ROW(Data!\$B\$3:\$B\$8)-MIN(ROW(Data!\$B\$3:\$B\$8))+1),COUNTIF(\$A\$2:\$A2,\$A2)))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Return the value from the adjacent row for the nth occ

Works perfectly thaks

• Re: Return the value from the adjacent row for the nth occ

You're welcome.

Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!