Find Last Match, if no match use previous Match

[vanhunk]

Find the last corresponding value that matches lookup value, or use corresponding value of previous lookup value if no match is found:

I am stumped. I have 2 columns, Column B contains averages and Column C contains month numbers. I also have another separate list of sequential month numbers e.g. 4 to 12 (April to December), these are the lookup values.
I want to extract the last average in the B column that corresponds to a given month number in the C column. There can be more than one pair of Average and Month for a given month number and I am only interested in the last one. This average value must then appear next to the corresponding month number in the separate list of sequential month numbers. If however, and this is where the problem lies, there is no corresponding month number in the C column, the average value that must appear next to the month number in the separate list of sequential month numbers must be the average value corresponding to the previous month number that did appear in the C column.

This must be done using formulas. An example of what is required is given at the bottom below.



I appreciate your time.

Kind Regards,
vanhunk

[snb]

Arrayformula:

In E21:

=INDEX($B$1:$B$17;MAX(($C$1:$C$17=IF(COUNTIF(C1:C17;C21)=0;C20;C21))*ROW($1 :$17));1)

[vanhunk]

@snb:

Problem, what I get is the following:

4		0.0	, instead of 26
5		59.3	, correct
6		59.3	, correct
7		35.7	, correct
8		35.0	, correct
9		33.3	, correct
10		33.3	, correct
11		0.0	, instead of 33.3
12		0.0	, instead of 33.3

Regards,
vanhunk

[snb]

I don't think there is a 'previous' to the first entry.

But for the others:

=IF(COUNTIF($C$1:C$17;$C21)=0;E20;INDEX($B$1:$B$17;MAX(($C$1:$C$17=$C21)*RO W($1:$17));1))

[vanhunk]

True, that makes it more complicated. For 11 and 12 though there are.

[snb]

That's what I provided.
What you indicated in your file for C20 is a 'reasoning failure' I fear