=([@ColumnA]=[ColumnA])*([@ColumnB]=[ColumnB])

[vadimtrofi]

please help me: =([@ColumnA]=[ColumnA])*([@ColumnB]=[ColumnB]) F9 {0:0:0:0:0:1:1:0:0:0:1:0} How to make an array formula to get the row number of the last of "1"?

 

[JustynaMK]

Hi vadimtrofi,

I'm not quite sure what are you storing in your table so it's hard to adjust your formula directly. Taking some similar formula as an example:
=(A1:A12)*(B2:B12)

Let's assume that the result of this formula (F9) is exactly the same:
{0:0:0:0:0:1:1:0:0:0:1:0}

If you want to retrieve the row number of the last "1", you need to use the following Array formula (Ctrl+Shift+Enter):
=LARGE(IF((A1:A12)*(B1:B12),ROW(A1:A12),""),1)

The result of this formula is 11. Hope you will find it helpful.

 

[AhoyNC]

Another option if you have Excel ver. 2010 or later may be something like this:

Excel Workbook
	A	B	C	D	E
1	t	r		t	y
2	u	y		7
3	i	u
4	t	y
5	r	r
6	e	y
7	t	y
8	i	u
9	o	y
Sheet

 

[vadimtrofi]

Thanks for your quick response.
I have found a solution myself ={MAX (IF ([@ColumnA]=[ColumnA]])*([@ColumnB]=[ColumnB])*([@ColumnC]=[ColumnC]),[ColumnD]))}
In [ColumnD] I have days of year, so I've got the last date - exactly what I need. I use Excel 2013.

 

[vadimtrofi]

The result of this formula is 11. Hope you will find it helpful.[/QUOTE]


It works. Thanks a lot.