Finding latest value in multiple columns that meets criteria and is bigger than 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• Jul 1, 2014

• #2

Hi,

What defines "latest"? And how do you get 170?

Regards

 

Upvote 0

Okoth

Board Regular

Joined

Sep 10, 2009

Messages

106

Office Version

1. 2019

Platform

1. Windows

• Jul 1, 2014

• #3

Hi,

With latest I mean the last row (A or E) that has B as a value.

How I get 170 is also my question, but the latest (last row with) B value is cell F10.

Thanks for looking at it.

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• Jul 1, 2014

• #4

What happens if the "latest" occurrence of "B" is the same in column B and column E? Do you take the value from column C or from column F?

Regards

 

Upvote 0

Okoth

Board Regular

Joined

Sep 10, 2009

Messages

106

Office Version

1. 2019

Platform

1. Windows

• Jul 1, 2014

• #5

The example I used is a very simple version of the sheet I am using. The latest B (the last row where we can find B that has a value bigger than 0) will always be either in column B or E. Never the same row. You can compare it with the World Cup playing schedule. Brazil (column A) is the home country in the first match, it is the home country in the second, but it is the away country (column E) in the third match

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• Jul 1, 2014

• #6

Thanks.

If you're using Excel 2010 or later:

=INDIRECT(TEXT(AGGREGATE(14,6,(ROW(A1:F13)*10^6+1+COLUMN(A1:F13))/((A1:F13=B15)*(B1:G13<>0)),1),"R000000C000000"),0)

Otherwise, array formula**:

=INDIRECT(TEXT(MAX(IF(A1:F13=B15,IF(B1:G13<>0,ROW(A1:F13)*10^6+1+COLUMN(A1:F13)))),"R000000C000000"),0)

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

Upvote 0

mikerickson

MrExcel MVP

Joined

Jan 15, 2007

Messages

24,355

• Jul 1, 2014

• #7

try the CSE formula
=INDEX(A1:F14,INT(MAX(($A$1:$E$13="b")*($B$1:$F$13<>0)*(ROW($A$1:$E$13)+COLUMN($B$1:$F$13)/100))), 100*MOD(MAX(($A$1:$E$13="b")*($B$1:$F$13<>0)*(ROW($A$1:$E$13)+COLUMN($B$1:$F$13)/100)), 1))

 

Upvote 0

Okoth

Board Regular

Joined

Sep 10, 2009

Messages

106

Office Version

1. 2019

Platform

1. Windows

• Jul 1, 2014

• #8

This works perfectly. No wonder I couldn't find the answer myself, how do you come up with something like this

 

Upvote 0

Okoth

Board Regular

Joined

Sep 10, 2009

Messages

106

Office Version

1. 2019

Platform

1. Windows

• Jul 1, 2014

• #9

XOR LX said:

Thanks.

If you're using Excel 2010 or later:

=INDIRECT(TEXT(AGGREGATE(14,6,(ROW(A1:F13)*10^6+1+COLUMN(A1:F13))/((A1:F13=B15)*(B1:G13<>0)),1),"R000000C000000"),0)

Click to expand...

Hi,

What do you mean with "R000000C000000"? Is this Row and Column? It gives an error on that point. I'm using Excel 2013.

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• Jul 1, 2014

• #10

Okoth said:

Hi,

What do you mean with "R000000C000000"? Is this Row and Column? It gives an error on that point. I'm using Excel 2013.

Click to expand...

Does your system use the comma or semi-colon as argument separator in functions?

Regards

 

Upvote 0