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

lpomykal

New Member
Joined
Dec 8, 2016
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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).
Capture.JPG
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,309
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
How about this

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
Joined
May 28, 2005
Messages
46,848
Office Version
  1. 365
Platform
  1. Windows
@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
Joined
May 28, 2005
Messages
46,848
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 8, 2016
Messages
7
Office Version
  1. 365
Platform
  1. Windows
How about this

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
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
46,848
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 8, 2016
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
46,848
Office Version
  1. 365
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,114,017
Messages
5,545,517
Members
410,689
Latest member
ConfuzzledThomas
Top