What is that slash in LOOKUP?

[fred3]

I have a formula that works but I don't know how to read the formula:

=+LOOKUP(2,1/($AZ$2:INDIRECT("AZ"&ROW())<>0))

What the heck is that slash after the 1 in the second argument? What does it do?
I can't find any examples with a slash like this in them...

 

[martindwilson]

1 no need for +
=LOOKUP(2,1/($AZ$2:INDIRECT("AZ"&ROW())<>0))
2
($AZ$2:INDIRECT("AZ"&ROW())<>0)
will return an array of
from az2 to whatever row the formula is in so say its in row 5
($AZ$2:INDIRECT("AZ"&ROW())
will be
az2:az5
it then looks to see which values do not = 0
and returns true or false
so you would get eg {true,false,false,true}
use 1/{true,false,false,true}
gives
{1,#DIV/0!,#DIV/0!,1}
lookup 2 at that array
since lookup ignores errors like #DIV/0!it will find the last value that is "
the largest value in the array that is less than or equal to lookup_value." in this case
{1,#DIV/0!,#DIV/0!,1}







,

 

[martindwilson]

also i cant see what the purpose of that formula is as it will only return n/a or 1
you need the second part of lookup
=LOOKUP(2,1/($AZ$2:INDIRECT("AZ"&row())<>0),$AZ$2:INDIRECT("AZ"&row()))
even then you wil have problems if the row is less than 2

 

[fred3]

It was a snippet. The entire cell contents are:

=IF(SUM(CR54:CW54)<>0,IF(INDIRECT("AU" & LOOKUP(2, 1/($AZ$2:INDIRECT("AZ" & ROW())<>0), ROW($AZ$2:INDIRECT("AZ" & ROW())) - ROW($AZ$2) + 2))=TRUE,BT54,0),0)

and I'd like to get rid of the INDRECT usage.

 

[fred3]

Anyway, Thank you for the insight on the "1/" !!