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?

Name Bob Sue Fred James Sarah Mark John Account Number 1234 1235 1286 1295 1267 1298 1288 Date 05/05/2019 10/06/2019 04/05/2019 01/06/2019 Note fvfvfvfzfvzf vcbgnbggc nbbvncvbnc hcfjlkdslflskd Date 07/05/2019 10/06/2019 Note fdsgvfbgfb nvkjlfnvlfnl Date 10/06/2019 Note nfjldsnvjsdfklnv Results 10/06/2019 Bob nfjldsnvjsdfklnv Sue vcbgnbggc James nvkjlfnvlfnl <colgroup><col span="5"><col span="3"></colgroup><tbody> </tbody>

<tbody>
</tbody>

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
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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?