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)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td]Date[/td][td]Peter[/td][td]John[/td][td]Kevin[/td][/tr]
[tr][td]

2​

[/td][td]

6/1/2018​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][/tr]
[tr][td]

3​

[/td][td]

7/1/2018​

[/td][td]

4​

[/td][td]

5​

[/td][td]

6​

[/td][/tr]
[tr][td]

4​

[/td][td]

8/1/2018​

[/td][td]

7​

[/td][td]

8​

[/td][td]

9​

[/td][/tr]
[tr][td]

5​

[/td][td]

6/1/2018​

[/td][td]

10​

[/td][td]

11​

[/td][td]

12​

[/td][/tr]
[tr][td]

6​

[/td][td]

7/1/2018​

[/td][td]

13​

[/td][td]

14​

[/td][td]

15​

[/td][/tr]
[tr][td]

7​

[/td][td]

8/1/2018​

[/td][td]

16​

[/td][td]

17​

[/td][td]

18​

[/td][/tr]
[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Summary (processing)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td]record #[/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]Name[/td][td]

6/1/2018​

[/td][td]

7/1/2018​

[/td][td]

8/1/2018​

[/td][/tr]
[tr][td]

3​

[/td][td]Peter[/td][td]

1​

[/td][td]

4​

[/td][td]

7​

[/td][/tr]
[tr][td]

4​

[/td][td]John[/td][td]

2​

[/td][td]

5​

[/td][td]

8​

[/td][/tr]
[tr][td]

5​

[/td][td]Kevin[/td][td]

3​

[/td][td]

6​

[/td][td]

9​

[/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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)


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Date​

[/td][td]

Peter​

[/td][td]

John​

[/td][td]

Kevin​

[/td][td][/td][td]

Name​

[/td][td]

Occurrence​

[/td][td]

June 1​

[/td][td]

July 1​

[/td][td]

August 1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

June 1​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td][/td][td]

Peter​

[/td][td]

1​

[/td][td]

1​

[/td][td]

4​

[/td][td]

7​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

July 1​

[/td][td]

4​

[/td][td]

5​

[/td][td]

6​

[/td][td][/td][td]

John​

[/td][td]

1​

[/td][td]

2​

[/td][td]

5​

[/td][td]

8​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

August 1​

[/td][td]

7​

[/td][td]

8​

[/td][td]

9​

[/td][td][/td][td]

Kevin​

[/td][td]

1​

[/td][td]

3​

[/td][td]

6​

[/td][td]

9​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

June 1​

[/td][td]

10​

[/td][td]

11​

[/td][td]

12​

[/td][td][/td][td]

Peter​

[/td][td]

2​

[/td][td]

10​

[/td][td]

13​

[/td][td]

16​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

July 1​

[/td][td]

13​

[/td][td]

14​

[/td][td]

15​

[/td][td][/td][td]

John​

[/td][td]

2​

[/td][td]

11​

[/td][td]

14​

[/td][td]

17​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

August 1​

[/td][td]

16​

[/td][td]

17​

[/td][td]

18​

[/td][td][/td][td]

Kevin​

[/td][td]

2​

[/td][td]

12​

[/td][td]

15​

[/td][td]

18​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.