Finding the nth value with index/match with a twist

[kkernohan]

Hi!

I have been trying to figure out how to do this one thing but cant for the life of me figure it out. I have vastly simplified it below, but essentially I am trying to have one formula that will "hlookup" off the name on the top and give the nth occurrence of a specific date going down. I cant simply do an Hlookup because sometimes i need the 2nd or 3rd occurrence of the date. Thanks in advance for any help!

I have a tab with multiple different Columns of Data for example;

Date Peter John Kevin
June 1 1 2 3
July 1 4 5 6
August 1 7 8 9
June 1 10 11 12
July 1 13 14 15
August 1 16 17 18


I then have a summary sheet looking like;

1st Occurrence
Name June 1 July 1 August 1
Peter 1 4 7
John 2 5 8
Kevin 3 6 9

2nd Occurrence
Name June 1 July 1 August 1
Peter 10 13 16
John 11 14 17
Kevin 12 15 18

 

[kkernohan]

I was using index/match but it wouldnt let me lookup the name going horizontally for the column that I wanted.

ex

   {=[URL="https://exceljet.net/excel-functions/excel-index-function"]INDEX[/URL](array,[URL="https://exceljet.net/excel-functions/excel-small-function"]SMALL[/URL]([URL="https://exceljet.net/excel-functions/excel-if-function"]IF[/URL](vals=val,[URL="https://exceljet.net/excel-functions/excel-row-function"]ROW[/URL](vals)-[URL="https://exceljet.net/excel-functions/excel-row-function"]ROW[/URL]([URL="https://exceljet.net/excel-functions/excel-index-function"]INDEX[/URL](vals,1,1))+1),nth))}

 

[Aladin Akyurek]

Sheet1 (data)

Row\Col	A​	B​	C​	D​
1​	Date	Peter	John	Kevin
2​	6/1/2018​	1​	2​	3​
3​	7/1/2018​	4​	5​	6​
4​	8/1/2018​	7​	8​	9​
5​	6/1/2018​	10​	11​	12​
6​	7/1/2018​	13​	14​	15​
7​	8/1/2018​	16​	17​	18​
8​

Summary (processing)

Row\Col	A​	B​	C​	D​
1​	record #	1​
2​	Name	6/1/2018​	7/1/2018​	8/1/2018​
3​	Peter	1​	4​	7​
4​	John	2​	5​	8​
5​	Kevin	3​	6​	9​
6​

In B3 control+shift+enter, not just enter, copy across, and down:

=INDEX(Sheet1!$B$2:$D$7,SMALL(IF(Sheet1!$A$2:$A$7=B$2,ROW(Sheet1!$A$2:$D$7)-ROW(INDEX(Sheet1!$A$2:$D$7,1,1))+1),$B$1),
     MATCH($A3,Sheet1!$B$1:$D$1,0))

 

[Marcelo Branco]

Maybe something like this (adjust the ranges as needed)

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Date​	Peter​	John​	Kevin​		Name​	Occurrence​	June 1​	July 1​	August 1​
2​	June 1​	1​	2​	3​		Peter​	1​	1​	4​	7​
3​	July 1​	4​	5​	6​		John​	1​	2​	5​	8​
4​	August 1​	7​	8​	9​		Kevin​	1​	3​	6​	9​
5​	June 1​	10​	11​	12​		Peter​	2​	10​	13​	16​
6​	July 1​	13​	14​	15​		John​	2​	11​	14​	17​
7​	August 1​	16​	17​	18​		Kevin​	2​	12​	15​	18​
8​

Array formula in G2 copied across and down
=INDEX(INDEX($B$2:$D$7,0,MATCH($F2,$B$1:$D$1,0)),SMALL(IF($A$2:$A$7=H$1,ROW($A$2:$A$7)-ROW($A$2)+1),$G2))
Ctrl+Shift+Enter

M.

 

[Marcelo Branco]

Correction: the formula above should be entered n H2, not in G2

M.