CODEWITHSUNDEEP
excelexcel-formula
Asho1000
Asho1000

Reputation: 21

Excel take nth to last value with multiple criteria

I need help with an Excel formula to extract the nth to last value in a data set with multiple criteria.

I have come across this formula which extracts the last (or nth to last) value where the specified name (A1) matches the range.

=Index(F:F,small(if(B:B=A1,row(B:G)-min(row(B:G))+1),countif(B:B,A1)))

I am taking the last three values so I have simply repeated this formula 3 times changing the nth to last number each time. This formula is working correctly to bring across the correct values.

However I have some additional data in column I which I only want to include the figure in column F if column I is blank. I have tried to include this as a second IF formula after the first one but it seems to be breaking the entire formula. For example =index(F:F,small(if(B:B=A1,if(I:I="",row(B:G)-min(row(B:G)))+1),countif(B:B,A1)))

In summary I am looking to return the last, 2nd last and 3rd last values in column F, where the value in column B matches A1 and the value in column I is blank.

Can anyone help me with the final piece of the formula? Thanks in advance.

Ash

Upvotes: 2

Views: 211

Answers (2)

nkalvi
nkalvi

Reputation: 2614

If you have Microsoft 365 (to use FILTER), could you try

=SMALL(
FILTER($F$2:$F$20,(B2:$B$20=$A$1) * ($I$2:$I$20=""))
,SEQUENCE(3))

Upvotes: 1

Black cat
Black cat

Reputation: 6177

This formula gives the below result integrated with testing I column.

=INDEX(F:F,SMALL(IF((B:B=A1)*(I:I=""),ROW(B:G)-MIN(ROW(B:G))+1),COUNTIFS(B:B,A1,I:I,"")-{0,1,2}))

It is recommended to define only the rows in the ranges which are really used because of reducing execution time.

enter image description here

Upvotes: 2

Related Questions