Find MAX Column In An Array

[excelbytes]

I need to find the last column that a certain person would appear. For example, in this table, I would want a formula that would result in 6/3/2024 for Person 2 (which is in cell I9. I used Power Query to unpivot the table then used MAXIFS to find that result, but was wondering if there was a formula way to accomplish this.

 

[Cubist]

Try

=MAX(IF(B3:K9="Person 2",B2:K2))

 

[Fluff]

How about

=INDEX(2:2,AGGREGATE(14,6,COLUMN(B2:K2)/(B2:K9="Person 2"),1))

 

[excelbytes]

Try

=MAX(IF(B3:K9="Person 2",B2:K2))

I entered this formula:

=MAX(IF(Sheet2!$B$3:$K$9=Sheet1!B3,Sheet2!$B$2:$K$2))

It just returned all zeros.

 

[excelbytes]

How about

=INDEX(2:2,AGGREGATE(14,6,COLUMN(B2:K2)/(B2:K9="Person 2"),1))

I entered this formula:

=INDEX(2:2,AGGREGATE(14,6,COLUMNS(Sheet2!$B$2:$K$2)/(Sheet2!$B$2:$K$9=B3),1))

I got this when I copied it down:

Fri

Y

1/0/1900​

Y

Y

Y

1/0/1900​

1/0/1900​

Y

#NUM!​

Y

What is the purpose of 2:2 for the INDEX range and is it supposed to change to 3:3, 4:4, etc as I copy it down?

 

[Fluff]

If your going to drag it down it needs to be

=INDEX(Sheet2!$2:$2,AGGREGATE(14,6,COLUMN(Sheet2!$B$2:$K$2)/(Sheet2!$B$2:$K$9=B3),1))

 

[excelbytes]

If your going to drag it down it needs to be

=INDEX(Sheet2!$2:$2,AGGREGATE(14,6,COLUMN(Sheet2!$B$2:$K$2)/(Sheet2!$B$2:$K$9=B3),1))

I changed the formula to:

=INDEX(Sheet2!$B$2:$K$2,AGGREGATE(14,6,COLUMNS(Sheet2!$B$2:$K$2)/(Sheet2!$B$2:$K$9=B3),1))

Now it just shows 6/5/2024 all the way down, which is correct for some of the Persons, but not all.

 

[Cubist]

I entered this formula:

=MAX(IF(Sheet2!$B$3:$K$9=Sheet1!B3,Sheet2!$B$2:$K$2))

It just returned all zeros.

That means 1) there are no match or 2) your date headers are text and not date.

 

[Fluff]

I changed the formula to:

Don't change it, Just try what I suggested.

 

[Cubist]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		5/27/24	5/28/24	5/29/24	5/30/24	5/31/24	6/1/24	6/2/24	6/3/24	6/4/24	6/5/24
3		Person 9	Person 3	Person 2	Person 7	Person 2	Person 7	Person 1	Person 4	Person 8	Person 1		Person 1	6/5/24
4		Person 1	Person 5	Person 6	Person 6	Person 1	Person 3	Person 5	Person 1	Person 2	Person 9		Person 2	6/4/24
5		Person 6	Person 7	Person 3	Person 8	Person 1	Person 9	Person 9	Person 8	Person 7	Person 5		Person 3	6/3/24
6		Person 7	Person 2	Person 8	Person 3	Person 3	Person 4	Person 2	Person 3	Person 7	Person 6		Person 4	6/3/24
7		Person 9	Person 1	Person 3	Person 2	Person 4	Person 3	Person 2	Person 7	Person 8	Person 8		Person 5	6/5/24
8		Person 6	Person 1	Person 7	Person 7	Person 3	Person 7	Person 3	Person 7	Person 5	Person 5		Person 6	6/5/24
9		Person 8	Person 5	Person 3	Person 1	Person 8	Person 7	Person 1	Person 2	Person 2	Person 6		Person 7	6/4/24
10													Person 8	6/5/24
11													Person 9	6/5/24
Sheet1

Cell Formulas
Range	Formula
C2:K2	C2	=B2+1
N3:N11	N3	=MAX(IF($B$3:$K$9=M3,$B$2:$K$2))