If I use Excel's Help on LOOKUP, ( LOOKUP function - Office Support ) amongst the informations is:
Important: The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.


To see what your lookup_vectors are, in the formula bar, select this section of the formula only:


1/SEARCH(Bead!A$2:A$94,E2)
and press F9 on the keyboard. You get this in the formula bar:
=LOOKUP(2,{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE! ;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;# VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VA LUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALU E!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;0.00318 471337579618;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALU E!;#VALUE!;#VALUE!;#VALUE!;0.00178571428571429;#VA LUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALU E!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE! ;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;# VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VA LUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALU E!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE! ;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;# VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VA LUE!;#VALUE!;#VALUE!;#VALUE!},Bead!A$2:A$94)


You're looking up 2 amongst a bunch of #VALUE!s and 2 numeric values (0.00318471337579618 & 0.00178571428571429) which aren't in any particular order (not ascending) so as the Important note says: LOOKUP might not return the correct value.


See also the other remarks (my red):

• If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.
• If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns the #N/A error value.

So don't use LOOKUP.