Find last occurrence of specific value in a row

Question

I have a table that keeps scores from fantasy football league. The lowest scoring week is not included in the total.

I'm able to find the lowest score using the =min function [=min(B2:R2)]

I can find the first instance using the =match function. [=MATCH(S2,B2:R2,0)]

However I would like a formula that returns the latest occurrence of the lowest scoring week.

For example Portia lowest score is 8 the first occurrence is week 4 but I would like it to return the latest occurrence which is week 11

Answer 1

You can also do this using LOOKUP function and avoiding array formulas, e.g. assuming S2 has the minimum value you can get the last week with that value with this formula in T2 copied down

=LOOKUP(2,1/(B2:R2=S2),B$1:R$1)

This works because (B2:R2=S2) returns an array of TRUE/FALSE values, and then dividing 1 by that array gives you an array of 1s or #DIV/0! errors. When you use 2 as the lookup value on that array it won't find any 2s so will therefore match with the last number (the last 1) in the array. That corresponds to the last week with your smallest value so using the top row as the "return vector" means that the formula returns the corresponding week number as required

Answer 2

You will need to use an Array Formula to achieve this. The following will return the "highest" lowest value if you are using columns B:R as the data columns and column T as the Low Week column. You need to enter this as an array (CSE) formula by copying and pasting it into cell T2 and pressing Ctrl+Shift+Enter.

=IFERROR(INDEX($A$1:$R$1,1,SMALL(IF(B2:R2=MIN(B2:R2),COLUMN(B2:R2),FALSE),COUNTIF(B2:R2,MIN(B2:R2)))),"")

Explanation

First, the COUNTIF(B2:R2,MIN(B2:R2)) will count the number of times the minimum value occurs. This is needed because a simpleMIN(B2:R2) will only grab the first minimum value, not the nth minimum value.

Next, the IF(B2:R2=MIN(B2:R2),COLUMN(B2:R2),FALSE) is the actual array formula part of the equation (the reason why you need to use Ctrl+Shift+Enter). It will only return the references for when you have a minimum and returning FALSE for all other entries greater than the minimum. Now, you have an array of only the column numbers the smallest values in the dataset.

Then, the SMALL(IF(B2:R2=MIN(B2:R2),COLUMN(B2:R2),FALSE),COUNTIF(B2:R2,MIN(B2:R2))) will take the column numbers of the smallest values and find the latest occurrence of this, which comes from the COUNTIF(B2:R2,MIN(B2:R2)) code.

Now that the last smallest occurrences' column number is know, you can use the INDEX function to find the value in the first row INDEX($A$1:$R$1,1.

Finally, the IFERROR will display the Low Week row if the SMALL function finds a match, otherwise it will display a blank cell. This could be used to copy this array formula further down the page for rows that you don't yet have users for.

Result