Look up value and return multiple results

[jakewatson]

Hi Guys,

I'm hoping you can help me.... I'm trying to create a simple spreadsheet in excel to keep track of my daily conversations. I have the spreadsheet laid out horizontally which I guess is not ideal (I can transpose this if it makes life easier). I'm hoping to put the date in each week and return a list of that weeks conversations. It's looking up the date across multiple rows and columns that I'm having difficulty with. If I can return the results for that day I can replicate that into my weekly sheet. Hope the Example below makes sense?[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 594"]
<colgroup><col span="5"><col span="3"></colgroup><tbody>[TR]
[TD]Name [/TD]
[TD]Bob[/TD]
[TD]Sue[/TD]
[TD]Fred[/TD]
[TD]James[/TD]
[TD]Sarah[/TD]
[TD]Mark[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Account Number[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]1235[/TD]
[TD="align: right"]1286[/TD]
[TD="align: right"]1295[/TD]
[TD="align: right"]1267[/TD]
[TD="align: right"]1298[/TD]
[TD="align: right"]1288[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD="align: right"]05/05/2019[/TD]
[TD="align: right"]10/06/2019[/TD]
[TD="align: right"]04/05/2019[/TD]
[TD="align: right"]01/06/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Note[/TD]
[TD]fvfvfvfzfvzf[/TD]
[TD]vcbgnbggc[/TD]
[TD]nbbvncvbnc[/TD]
[TD="colspan: 2"]hcfjlkdslflskd[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD="align: right"]07/05/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/06/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Note[/TD]
[TD]fdsgvfbgfb[/TD]
[TD][/TD]
[TD][/TD]
[TD]nvkjlfnvlfnl[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD="align: right"]10/06/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Note[/TD]
[TD="colspan: 2"]nfjldsnvjsdfklnv[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Results[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/06/2019[/TD]
[TD] Bob[/TD]
[TD="colspan: 2"]nfjldsnvjsdfklnv[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Sue[/TD]
[TD]vcbgnbggc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] James[/TD]
[TD]nvkjlfnvlfnl[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks!!!

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H
1	Name	Bob	Sue	Fred	James	Sarah	Mark	John
2	Account Number	1234	1235	1286	1295	1267	1298	1288
3	Date	05/05/2019	10/06/2019	04/05/2019	01/06/2019
4	Note	fvfvfvfzfvzf	vcbgnbggc	nbbvncvbnc	hcfjlkdslflskd
5	Date	07/05/2019			10/06/2019
6	Note	fdsgvfbgfb			nvkjlfnvlfnl
7	Date	10/06/2019
8	Note	nfjldsnvjsdfklnv
9
10	Results
11	10/06/2019	Bob	nfjldsnvjsdfklnv
12		Sue	vcbgnbggc
13		James	nvkjlfnvlfnl
Sheet6

Cell Formulas
Range	Formula
C11	=INDEX(INDEX($A$1:$H$8,,MATCH(B11,$A$1:$H$1,0)),MATCH($A$11,INDEX($A$1:$H$8,,MATCH(B11,$A$1:$H$1,0)),0)+1)

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H
1	Name	Bob	Sue	Fred	James	Sarah	Mark	John
2	Account Number	1234	1235	1286	1295	1267	1298	1288
3	Date	5/5/2019	10/6/2019	4/5/2019	1/6/2019
4	Note	fvfvfvfzfvzf	vcbgnbggc	nbbvncvbnc	hcfjlkdslflskd
5	Date	7/5/2019			10/6/2019
6	Note	fdsgvfbgfb			nvkjlfnvlfnl
7	Date	10/6/2019
8	Note	nfjldsnvjsdfklnv
9
10	Results
11	10/6/2019	Bob	nfjldsnvjsdfklnv
12		Sue	vcbgnbggc
13		James	nvkjlfnvlfnl
14
Sheet1

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

=IFERROR(INDEX($B$1:$H$1,SMALL(IF($B$3:$H$7=$A$11,IF($B$4:$H$8<>"",COLUMN($B$3:$H$7)-COLUMN($B$3)+1)),ROWS($1:1))),"")

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

=IFERROR(INDEX($B$4:$H$8,MIN(IF($B$1:$H$1=$B11,IF($B$3:$H$7=$A$11,IF($B$4:$H$8<>"",ROW($B$4:$H$8)-ROW($B$4)+1)))),MATCH($B11,$B$1:$H$1,0)),"")

 

[jakewatson]

	A	B	C	D	E	F	G	H
Name	Bob	Sue	Fred	James	Sarah	Mark	John
Account Number
Date
Note	fvfvfvfzfvzf	vcbgnbggc	nbbvncvbnc	hcfjlkdslflskd
Date
Note	fdsgvfbgfb	nvkjlfnvlfnl
Date
Note	nfjldsnvjsdfklnv
Results
Bob	nfjldsnvjsdfklnv
Sue	vcbgnbggc
James	nvkjlfnvlfnl

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1234[/TD]
[TD="align: right"]1235[/TD]
[TD="align: right"]1286[/TD]
[TD="align: right"]1295[/TD]
[TD="align: right"]1267[/TD]
[TD="align: right"]1298[/TD]
[TD="align: right"]1288[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5/5/2019[/TD]
[TD="align: right"]10/6/2019[/TD]
[TD="align: right"]4/5/2019[/TD]
[TD="align: right"]1/6/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]7/5/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10/6/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]10/6/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10/6/2019[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

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

=IFERROR(INDEX($B$1:$H$1,SMALL(IF($B$3:$H$7=$A$11,IF($B$4:$H$8<>"",COLUMN($B$3:$H$7)-COLUMN($B$3)+1)),ROWS($1:1))),"")

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

=IFERROR(INDEX($B$4:$H$8,MIN(IF($B$1:$H$1=$B11,IF($B$3:$H$7=$A$11,IF($B$4:$H$8<>"",ROW($B$4:$H$8)-ROW($B$4)+1)))),MATCH($B11,$B$1:$H$1,0)),"")

That's perfect!!! THANK YOU!!

 

[jakewatson]

Hi,

Sorry I've hit a snag, if you have more than one note on the same person on the same day it just repeats the same notes. Is there a way round this please?