Hello,
I'm trying to use the LOOKUP function to return the header for the last FALSE in an array. Here is an example of the data set:
|
A |
B |
C |
D |
1 |
2019 |
2018 |
2017 |
2016 |
2 |
TRUE |
FALSE |
FALSE |
TRUE |
3 |
TRUE |
FALSE |
TRUE |
TRUE |
4 |
TRUE |
TRUE |
FALSE |
TRUE |
The code I'm using in to find the header for the last FALSE value in the second row is:
=LOOKUP(FALSE,A2:D2,A1:D1)
I would expect that for each row, the LOOKUP would function return an error because LOOKUP expects data to be sorted in ascending order, the first entry in the lookup array is TRUE, and it's looking for FALSE, but that's not what happens.
If the first value in the lookup array is TRUE and the second value is FALSE, the function returns the second value in the results array (in this case, 2018). But, if the first and second lookup values are TRUE (like in the fourth row), the LOOKUP function throws an error.
Can anyone explain to me what's happening? Is there a formula that will give me the year with the last FALSE in each row (so for the second row, it should be 2017, for the third row 2018, and for the fourth row 2017).
Thank you!
Bookmarks