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 https://answers.microsoft.com/en-us/msoffice/forum/all/how-some-function-like-lookup-vlookup-match/411d17a2-3488-4e83-9dbd-c29d8512a3e6?LastReply=true#LastReply (there are others)
