You can change the cell references if you know you will have a smaller list, this formula is easier for Excel to process:
The formula takes all cell values in column B and checks if they are not empty. Then it divides 1 with the array creating errors for all empty cells.
The LOOKUP function ignores errors and tries to find a match. If every match is 1 and the lookup value is 2, the LOOKUP function returns last value in cell range. Normally the list must be sorted ascending for the LOOKUP function to work, however since every value is 1 there is no need to sort the list.
I am going to use the following formula because it will be easier to demonstrate:
Step 1 - Check if cells in column B are not equal to nothing
I am working with Excel 2016 and errors in column B seems to not be an issue.
Return adjacent value
This data set has two columns, column B and C.
Formula in cell E3:
The formula returns an adjacent value of the last value. In fact, it doesn't need to be adjacent, you can change the cell reference (C:C) as long as it's starting point and ending point is the same as the first cell reference (B:B).
Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]