Formula to find a value by going right, down, and then left.

[lpomykal]

I am looking for a formula that will find the value in cell B1 in the row B5:I5. Then go down from that date to the first non-blank cell. Then go left and return the corresponding value/name in column A6:A12 into cell F1.

Thanks in advance. I have been racking my head trying to figure this out. I was using =INDEX($A$6:$A$12,SMALL(IF($H$6:$H$12<>"",ROW($H$6:$H$12)-ROW($H$6)+1),ROW(A1))) to find the nth non-blank cell and return the corresponding name, but that requires the column to be called out in the IF formula. For this I tried to use =MATCH(B1,B5:I5,0). This just gives me the column number within the range. I can't figure out how to combine the two formulas (if that is even possible).

 

[DRSteele]

I think this is what you're after. There is probably a simpler way, but this seems to work.

MrExcel posts18.xlsx
	A	B	C	D	E	F	G	H	I
1		1/7/2020			desc	M
2					value	12
3					single cell	M 12
4
5	desc	1/1/2020	1/2/2020	1/3/2020	1/4/2020	1/5/2020	1/6/2020	1/7/2020	1/8/2020
6	D		12
7	J	11	11						15
8	M		10					12
9	T	5	9
10	I		8
11	A		7
12	G		6					13
Sheet40

Cell Formulas
Range	Formula
F1	F1	=IFNA(INDEX(A6:A12,MATCH(TRUE(),ISNUMBER(INDEX(B6:I12,,MATCH(B1,B5:I5,0))),0)),"No Match")
F2	F2	=IFNA(INDEX(B6:I12,MATCH(TRUE(),ISNUMBER(INDEX(B6:I12,,MATCH(B1,B5:I5,0))),0),MATCH(B1,B5:I5,0)),"No Match")
F3	F3	=IFNA(INDEX(A6:A12,MATCH(TRUE(),ISNUMBER(INDEX(B6:I12,,MATCH(B1,B5:I5,0))),0))&" "&INDEX(B6:I12,MATCH(TRUE(),ISNUMBER(INDEX(B6:I12,,MATCH(B1,B5:I5,0))),0),MATCH(B1,B5:I5,0)),"No Match")

 

[CA_Punit]

How about this

Book1
	A	B	C	D	E	F
1		02/02/2020	Michelle
2
3
4		01/02/2020	02/02/2020	03/02/2020	04/02/2020
5	David
6	Jeff	11
7	Michelle		1
8	Tony	9
9	Tim
10	Alex		1
11	George
12
13
Sheet3

Cell Formulas
Range	Formula
C1	C1	=INDEX(A5:A11,AGGREGATE(15,6,(((INDEX(B5:E11,,MATCH(B1,B4:E4))&"")+0)^0)*(ROW(A5:A11)-ROW(A5)+1),1))

 

[Peter_SSs]

@lpomykal
I suggest that

1. You update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
   
   
2. Investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

In relation to point 1, if you have Excel 365 with the FILTER function, then you could try adapting this

20 07 31.xlsm
	A	B	C	D	E
1		2/02/2020	Michelle
2
3
4
5		1/02/2020	2/02/2020	3/02/2020	4/02/2020
6	David
7	Jeff	11
8	Michelle		1
9	Tony	9
10	Tim
11	Alex		1
12	George
Ipomykal

Cell Formulas
Range	Formula
C1	C1	=INDEX(FILTER(A6:A12,FILTER(B6:E12,B5:E5=B1)<>"","N/A"),1)

 

[Peter_SSs]

if you have Excel 365 with the FILTER function,

If not, another alternative is below. Confirm formula with Ctrl+Shift+Enter, not just Enter

20 07 31.xlsm
	A	B	C	D	E
1		2/02/2020	Michelle
2
3
4
5		1/02/2020	2/02/2020	3/02/2020	4/02/2020
6	David
7	Jeff	11
8	Michelle		1
9	Tony	9
10	Tim
11	Alex		1
12	George
Ipomykal

Cell Formulas
Range	Formula
C1	C1	=INDEX(A6:A12,MATCH(TRUE,INDEX(B6:E12,0,MATCH(B1,B5:E5,0))<>"",0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[lpomykal]

How about this

Book1
	A	B	C	D	E	F
1		02/02/2020	Michelle
2
3
4		01/02/2020	02/02/2020	03/02/2020	04/02/2020
5	David
6	Jeff	11
7	Michelle		1
8	Tony	9
9	Tim
10	Alex		1
11	George
12
13
Sheet3

Cell Formulas
Range	Formula
C1	C1	=INDEX(A5:A11,AGGREGATE(15,6,(((INDEX(B5:E11,,MATCH(B1,B4:E4))&"")+0)^0)*(ROW(A5:A11)-ROW(A5)+1),1))

This is exactly what I was needing. Thank you. I can also find the 2nd or 3rd non-blank cells by changing the last 1 to 2 or 3 or etc.

 

[CA_Punit]

You can just cell reference the {1} in the formula so that you need not change it every time manually.

 

[Peter_SSs]

I can also find the 2nd or 3rd non-blank cells by changing the last 1 to 2 or 3 or etc

You can also change the 1 to 2 or 3 near the end of the formula from post 4 or if you want a list of up to, say, 3 values you could use this, copied down.

20 07 31.xlsm
	A	B	C	D	E
1		3/02/2020	Jeff
2			Tony
3			Alex
4
5		1/02/2020	2/02/2020	3/02/2020	4/02/2020
6	David
7	Jeff	11		2
8	Michelle		1
9	Tony	9		18
10	Tim		5
11	Alex	7		33
12	George			35
Ipomykal

Cell Formulas
Range	Formula
C1:C3	C1	=IFERROR(INDEX(FILTER(A$6:A$12,FILTER(B$6:E$12,B$5:E$5=B$1)<>"","N/A"),ROWS(C$1:C1)),"")

 

[lpomykal]

You can also change the 1 to 2 or 3 near the end of the formula from post 4 or if you want a list of up to, say, 3 values you could use this, copied down.

20 07 31.xlsm
	A	B	C	D	E
1		3/02/2020	Jeff
2			Tony
3			Alex
4
5		1/02/2020	2/02/2020	3/02/2020	4/02/2020
6	David
7	Jeff	11		2
8	Michelle		1
9	Tony	9		18
10	Tim		5
11	Alex	7		33
12	George			35
Ipomykal

Cell Formulas
Range	Formula
C1:C3	C1	=IFERROR(INDEX(FILTER(A$6:A$12,FILTER(B$6:E$12,B$5:E$5=B$1)<>"","N/A"),ROWS(C$1:C1)),"")

Thank you. I usually use ROW(A1) and drag that down, but this makes more since so I can reference a specific table.

 

[Peter_SSs]

Thank you.

You're welcome.

I usually use ROW(A1) and drag that down

I would advise to always use a ROWS() construct, referencing the row(s) that the formula is in. With your method if any row(s) are subsequently inserted at the top of the sheet the sequence will become 2, 3, 4, ... or 7, 8, 9, .. rather than always being 1, 2, 3, ... & the formula involved will return incorrect results (which may not always be obvious).