Large formula looking at a pivot table that updates as you select from data validation

putting aside efficiency, for now...

1. there is an error in your concatenate formula - what exactly are you trying to return?
if B1 encased in quotations use CHAR(34)&$B$1&CHAR(34) or """"&$B$1&""""

2. what is the value in A17?
if the numeric value is <= 0 you will get #NUM!


if, having accounted for the above, you're getting a #NUM! error when you think there should be a value returned....

do a COUNTIF check to verify how many matches you're actually going to find (will highlight discrepancies between criteria & source), i.e.:

=COUNTIF(Lookup!$C:$C,CHAR(34)&$B$1&CHAR(34))

in short, you would only get #NUM! at that point should A17 exceed the count of matches, or you happen to have #NUM! errors in your source data.

finally, on an aside, I would avoid using function names as tab names - i.e. use something other than LOOKUP (even if just a prefix/suffix)