CODEWITHSUNDEEP
excelvbaexcel-formula
user8829737
user8829737

Reputation:

Excel Formula return the value beside the latest date

I want to return the Card used to B2 based on the table to the right.

This returned value should be of the latest date(column G), and the cell to the left of the date should not be blank. If it is blank, return the next latest value.

I have this formula but I want it to return Bank B onto B3 instead of "0" how do i go about achieving this?

   =INDEX($F$2:$F$7, MATCH(A2 & MAX(IF($E$2:$E$7=A2,$G$2:$G$7)), $E$2:$E$7 & 
  $G$2:$G$7, 0))

enter image description here

Upvotes: 1

Views: 1276

Answers (2)

user4039065
user4039065

Reputation:

Try INDEX/AGGREGATE with a nested AGGREGATE for the conditional MAXIFS (assuming you don't have MAXIFS).

enter image description here

Upvotes: 0

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

For this, you need a complex array formula:

=INDEX($F$2:$F$7;MATCH(MAX(IF(IF($F$2:$F$7<>"";$E$2:$E$7)=A2;$G$2:$G$7))&A2;$G$2:$G$7&$E$2:$E$7;0);1)

IMPORTANT!: Because it is an array formula, you will need to type it as usual, and then, instead of pressing Enter press CTRL+SHIFT+ENTER

I've tested in my Excel and got this:

enter image description here

Upvotes: 3

Related Questions