Why don't these LOOKUP functions work?

[tigerzen]

Book4
	A	B	C	D	E	F	G	H	I
1		HR	Sales	Finance	Existing department
2	Tom		Yes		#N/A		FALSE	TRUE	FALSE
3	Pete	Yes			#N/A		TRUE	FALSE	FALSE
4	Fred				#N/A		FALSE	FALSE	FALSE
5	Sally			Yes	Yes		FALSE	FALSE	TRUE
Sheet1

Cell Formulas
Range	Formula
E2	E2	=LOOKUP(1,B2:D2<>"",B2:D2)
E3:E4	E3	=LOOKUP("TRUE",(B3:D3<>""),B3:D3)
E5	E5	=LOOKUP(2,1/(B5:D5<>""),B5:D5)
G2:I5	G2	=B2:D2<>""
Dynamic array formulas.

Trying to understand why the formulae in Rows 2 and 3 show errors, ie for Tom and Pete. Columns G to I with the relevant tests show that there are TRUE values but they aren't being picked up by the Lookup values in the formulae.

 

[SanjayGMusafir]

=LOOKUP(1,B2:D2<>"",B2:D2)

in E2 try

=LOOKUP(1,1/(B2:D2<>""),B2:D2)

 

[tigerzen]

in E2 try

=LOOKUP(1,1/(B2:D2<>""),B2:D2)

Thanks Sanjay, that does indeed work but I'm trying to grasp why the other formulae don't work. The division by 1 converts TRUE/FALSE to numbers but shouldn't the formulae for Pete work? Also noticed that if I change the formula to LOOKUP(1,1*(B2:D2<>""),B2:D2) and copy down, it only works for the first record, see attached spreadsheet.

Book4
	A	B	C	D	E	F	G	H	I
1		HR	Sales	Finance	Existing department
2	Tom		Yes		Yes		FALSE	TRUE	FALSE
3	Pete	Yes			0		TRUE	FALSE	FALSE
4	Fred				0		FALSE	FALSE	FALSE
5	Sally	Yes			0		TRUE	FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=LOOKUP(1,1*(B2:D2<>""),B2:D2)
G2:I5	G2	=B2:D2<>""
Dynamic array formulas.

 

[Peter_SSs]

Why don't these LOOKUP functions work?

To answer your question ..
E2 formula does not work because it is looking for numbers in B2:D2 are there are no numbers. Hence the #N/A result.
E3:E4 formula does not work because it is looking for a text value but (B3:D3<>"") produces an array of logical True/False results, not text "True"/"False" results. Hence the #N/A result.

Here are three other options that you could consider, depending on whether it is possible for somebody to be in more than one department (see row 6).

23 05 02.xlsm
	A	B	C	D	E	F	G
1		HR	Sales	Finance	Existing department	Existing department	Existing department
2	Tom		Yes		Yes	Yes	Yes
3	Pete	Yes			Yes	Yes	Yes
4	Fred				#N/A	No
5	Sally			Yes	Yes	Yes	Yes
6	Ann	Yes		Yes	Yes	Yes	YesYes
Existing Dept

Cell Formulas
Range	Formula
E2:E6	E2	=LOOKUP("z",B2:D2)
F2:F6	F2	=IF(COUNTIF(B2:D2,"Yes"),"Yes","No")
G2:G6	G2	=CONCAT(B2:D2)

 

[tigerzen]

Thanks Peter, that makes sense. I have a follow-up question, in Post #3 why does that formula appear to work only for the first record?

 

[Peter_SSs]

... in Post #3 why does that formula appear to work only for the first record?

Because in that formula, LOOKUP is looking for numbers and if there are multiple numbers, it expects them to be in ascending order, If not, results can be unpredictable. See the "Important" section under "Syntax" in the LOOKUP Help

In your row 3 the formula is
=LOOKUP(1,1*(B3:D3<>""),B3:D3)
=LOOKUP(1,1*{True, False, False},B3:D3)
=LOOKUP(1,{1,0,0},B3:D3)
Those numbers are not in ascending order so you are subject to the "unpredictable results".

The equivalent formula suggested in post #2 is
=LOOKUP(1,1/(B3:D3<>""),B3:D3)
=LOOKUP(1,1/{True, False, False},B3:D3)
=LOOKUP(1,{1,{1,#DIV/0!,#DIV/0!},B3:D3)
Here there is only one number so the numbers are in ascending order and so the formula works.

 

[tigerzen]

Because in that formula, LOOKUP is looking for numbers and if there are multiple numbers, it expects them to be in ascending order, If not, results can be unpredictable. See the "Important" section under "Syntax" in the LOOKUP Help

In your row 3 the formula is
=LOOKUP(1,1*(B3:D3<>""),B3:D3)
=LOOKUP(1,1*{True, False, False},B3:D3)
=LOOKUP(1,{1,0,0},B3:D3)
Those numbers are not in ascending order so you are subject to the "unpredictable results".

The equivalent formula suggested in post #2 is
=LOOKUP(1,1/(B3:D3<>""),B3:D3)
=LOOKUP(1,1/{True, False, False},B3:D3)
=LOOKUP(1,{1,{1,#DIV/0!,#DIV/0!},B3:D3)
Here there is only one number so the numbers are in ascending order and so the formula works.

Thank you for the explanation, it helps to solidify the concept. It seems a powerful formula and is so versatile.

 

[Peter_SSs]

You're welcome. Glad to help.