# Return value in adjacent column for nth occurrence

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• I'll let you know up front that I'm completely new to this and may be prone to stupid errors. What I'm trying to do is search one column for the nth occurrence of a specific item, then report the value of the adjacent cell. The example I'm using is a fantasy football draft. I want to report the first selection made by one owner in a column bearing his name. Below that, I want to return the second selection, then the third, and so on.

Essentially:

In the table to the left, I have a list of draft picks in the order they occur. I want the table to the right to populate with the draft pick each player makes in the order he makes that pick. I want the program to recognize the first time "Aaron" appears in Column C, then take the corresponding value in Column D and report it in the first cell under "Aaron" in Column G. Then the second time needs to be reported in the second cell under his name and so on.

I've tried to use a number of different Excel functions, but I don't think I really understand the syntax and am failing to get Excel (or Google Spreadsheet) to understand what I'm requesting.

## Files

• Re: Return value in adjacent column for nth occurrence

=IFERROR(INDEX(\$D\$2:\$D\$25,SMALL(IF(\$C\$2:\$C\$25=G\$1,ROW(\$C\$2:\$C\$25)-ROW(\$C\$2)+1),ROWS(\$C\$2:C2))),"")
Confirm Contrl+Shift+enter Drag down and accross

• Re: Return value in adjacent column for nth occurrence

Quote from Robert Mika;682658

=IFERROR(INDEX(\$D\$2:\$D\$25,SMALL(IF(\$C\$2:\$C\$25=G\$1,ROW(\$C\$2:\$C\$25)-ROW(\$C\$2)+1),ROWS(\$C\$2:C2))),"")
Confirm Contrl+Shift+enter Drag down and accross

Thank you. That works perfectly for cell G1, but I can't seem to copy down. Control+Shift+Enter doesn't work (at least not on this laptop, which is a Mac) and I haven't been able to find another way to enter it as an array formula. When I drag down, everything below G1 is blank. When I drag across, everything says "Peterson"

• Re: Return value in adjacent column for nth occurrence

worked for me on excel 2007/windows 7.

Just use the fillhandle and doubleclick.

• Re: Return value in adjacent column for nth occurrence

I managed to get it to work on my PC. Thank you, this is excellent!

• Re: Return value in adjacent column for nth occurrence

You are welcome.

• Re: Return value in adjacent column for nth occurrence

Hey this is very similar to what I was looking for. But how would I change the formulae if the values to be matched which in this case ere row G,H I were instead listed in one single column. Such as G1 ,G2 ,G3

• Re: Return value in adjacent column for nth occurrence

Attached an workbook with example.

## Participate now!

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