Lookup 2,1

[Jplace]

Hi

I vaguely understand Lookup2,1 but what I don't fully understand about the below formula. Is if for example the Rows C-H have values that are not equal to 0 why does the formula return the value in Column H rather than the value in column C?

=LOOKUP(2,1/('Daily 2017'!$C$15:$IR$15<>0),TEXT(ROUND('Daily 2017'!$C$15:$IRO$15,0),"£0,#k"))

Thanks

 

[Jonmo1]

It goes something like this
=LOOKUP(2,1/('Daily 2017'!$C$15:$IR$15<>0),

'Daily 2017'!$C$15:$IR$15<>0
This is an expression (a question) with a true or false answer for each cell in the range
Is C15 <> 0 ?
Is D15 <> 0 ?
Is E15 <> 0 ?
Etc..
So you get an array of Trues and falses, somethig like
=LOOKUP(2,1/{True,True,False,True,False,False},

When a math operation like +-*/ is applied to a True/False value, TRUE=1 FALSE=0
=LOOKUP(2,1/{1,1,0,1,0,0},

1/1 = 1
1/0 = #Div/0! error
=LOOKUP(2,{1,1,#Div/0!,1,#Div/0!,#Div/0!},

Fortunately, lookup ignores error values
So the 2 is larger than all numeric values in the array, so it uses the last number of the array (the 3rd 1 in the array)

Hope that helps.

 

[Jplace]

That makes sense at at last!

Thanks Jonmo1

 

[Jonmo1]

You're welcome.