LOOKUP with TRUE/FALSE Values

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:

Please Login or Register  to view this content.

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!

Welcome to the forum!

I don't understand how your formula relates to the table. The formula is dealing with columns, yet the table is arranged in rows. If the years are in row 1, then the formula doersn't even cover them.

If you want the year for each row, why are you testing the column range, and not the row range?

Please Login or Register  to view this content.

Another one
E2
=LOOKUP(1,0/NOT(A2:D2),A$1:D$1)

And another:

=LOOKUP(2,1/(A2:D2=FALSE),A$1:D$1)

And another.

=LOOKUP(2,1/(1-A2:D2),$A$1:$D$1)