Forum Discussion
Second to last value from table with condition
Hi experts Can you help me with the below? I have a list of cash values in a table, example as follows by a rotating week number (1-4). A ------------ B 1 ----------- 40 2------------- ...
You can use the following formula to get the second to last value for a specific week number:
=INDEX(B:B,MAX(IF(A:A=week_number,ROW(B:B)-ROW(B1)+1)))
Replace week_number with the week number you're interested in.
This is an array formula that uses the INDEX and MAX functions. Here's how it works:
- IF(A:A=week_number,ROW(B:B)-ROW(B1)+1) creates an array of row numbers where the week number matches week_number. The ROW(B:B)-ROW(B1)+1 part is used to adjust the row numbers to start at 1, since the formula will return an array starting from the first row.
- MAX(IF(A:A=week_number,ROW(B:B)-ROW(B1)+1)) finds the maximum row number in the array, which will be the row number of the last non-blank cell in the specified week.
- INDEX(B:B,MAX(IF(A:A=week_number,ROW(B:B)-ROW(B1)+1))) returns the value in column B at the row number found in step 2.
Make sure to enter this formula as an array formula by pressing Ctrl+Shift+Enter instead of just Enter. This will add curly braces around the formula in the formula bar to indicate that it's an array formula.