Forum Discussion
Re: Lookup function with specific cells
Thank you it worked !
Would you mind explaining what does the 2, 1/ stand for though ? I think I've understood how the rest of the formula works, but not this part... Thanks !
Would you mind explaining what does the 2, 1/ stand for though ? I think I've understood how the rest of the formula works, but not this part... Thanks !
2 Replies
As a comment to Lorenzo comment, instead of 2 as first parameter you may use any number greater than 1, for example PI().
(ISNUMBER($L4:$W4)*($L$3:$W$3=H$3)) generates an array like {1,0,0,0,1,0...}
1/(ISNUMBER($L4:$W4)*($L$3:$W$3=H$3)) gen. array {1,#DIV/0!,#DIV/0!,#DIV/0!,1,#DIV/0!..}
for LOOKUP(2, {1,#DIV/0!,#DIV/0!,#DIV/0!,1,#DIV/0!..},...) you could refer i.e. to this post (there are others)
