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

#### lpomykal

##### New Member
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).

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### DRSteele

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

MrExcel posts18.xlsx
ABCDEFGHI
11/7/2020descM
2value12
3single cellM 12
4
5desc1/1/20201/2/20201/3/20201/4/20201/5/20201/6/20201/7/20201/8/2020
6D12
7J111115
8M1012
9T59
10I8
11A7
12G613
Sheet40
Cell Formulas
RangeFormula
F1F1=IFNA(INDEX(A6:A12,MATCH(TRUE(),ISNUMBER(INDEX(B6:I12,,MATCH(B1,B5:I5,0))),0)),"No Match")
F2F2=IFNA(INDEX(B6:I12,MATCH(TRUE(),ISNUMBER(INDEX(B6:I12,,MATCH(B1,B5:I5,0))),0),MATCH(B1,B5:I5,0)),"No Match")
F3F3=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

##### Well-known Member

Book1
ABCDEF
102/02/2020Michelle
2
3
401/02/202002/02/202003/02/202004/02/2020
5David
6Jeff11
7Michelle1
8Tony9
9Tim
10Alex1
11George
12
13
Sheet3
Cell Formulas
RangeFormula
C1C1=INDEX(A5:A11,AGGREGATE(15,6,(((INDEX(B5:E11,,MATCH(B1,B4:E4))&"")+0)^0)*(ROW(A5:A11)-ROW(A5)+1),1))

#### Peter_SSs

##### MrExcel MVP, Moderator
@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
ABCDE
12/02/2020Michelle
2
3
4
51/02/20202/02/20203/02/20204/02/2020
6David
7Jeff11
8Michelle1
9Tony9
10Tim
11Alex1
12George
Ipomykal
Cell Formulas
RangeFormula
C1C1=INDEX(FILTER(A6:A12,FILTER(B6:E12,B5:E5=B1)<>"","N/A"),1)

#### Peter_SSs

##### MrExcel MVP, Moderator

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
ABCDE
12/02/2020Michelle
2
3
4
51/02/20202/02/20203/02/20204/02/2020
6David
7Jeff11
8Michelle1
9Tony9
10Tim
11Alex1
12George
Ipomykal
Cell Formulas
RangeFormula
C1C1=INDEX(A6:A12,MATCH(TRUE,INDEX(B6:E12,0,MATCH(B1,B5:E5,0))<>"",0))
Press CTRL+SHIFT+ENTER to enter array formulas.

#### lpomykal

##### New Member

Book1
ABCDEF
102/02/2020Michelle
2
3
401/02/202002/02/202003/02/202004/02/2020
5David
6Jeff11
7Michelle1
8Tony9
9Tim
10Alex1
11George
12
13
Sheet3
Cell Formulas
RangeFormula
C1C1=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

##### Well-known Member

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

#### Peter_SSs

##### MrExcel MVP, Moderator
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
ABCDE
13/02/2020Jeff
2Tony
3Alex
4
51/02/20202/02/20203/02/20204/02/2020
6David
7Jeff112
8Michelle1
9Tony918
10Tim5
11Alex733
12George35
Ipomykal
Cell Formulas
RangeFormula
C1:C3C1=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

##### New Member
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
ABCDE
13/02/2020Jeff
2Tony
3Alex
4
51/02/20202/02/20203/02/20204/02/2020
6David
7Jeff112
8Michelle1
9Tony918
10Tim5
11Alex733
12George35
Ipomykal
Cell Formulas
RangeFormula
C1:C3C1=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

##### MrExcel MVP, Moderator
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).

Replies
4
Views
102
Replies
4
Views
597
Replies
5
Views
100
Replies
10
Views
294
Replies
19
Views
268