Problems Using LOOKUP() in Excel

Question

I want to use the LOOKUP function to match values according to their category (see picture).

It worked so far as expected, but when I enlarge the range of the lookup_vector, it breaks, meaning that once I get the expected value, once #N/A.

In one row I have =LOOKUP(T2,'Electrode Clalc'!P3:P36,'Electrode Clalc'!E3:E36) , in the other I have =LOOKUP(T3,'Electrode Clalc'!P3:P37,'Electrode Clalc'!E3:E37).

Case 1 where it works:

Case 2 where it doesn't:

P36/E36 and P37/E36 contain both similar code:

P36: =0.8*(D36-N33)*(1-LOOKUP(A32,'LFP Calculations'!A2:A32,'LFP Calculations'!E2:E32)) E36: =CONCATENATE(LEFT(A32, 100),"-",LEFT(B32, 100),"-",LEFT(C36,100))

and

P37:=0.8*(D37-N33)*(1-LOOKUP(A32,'LFP Calculations'!A2:A32,'LFP Calculations'!E2:E32)) E37:=CONCATENATE(LEFT(A32, 100),"-",LEFT(B32, 100),"-",LEFT(C37,100))

I would appreciate if someone could help me. Thank you in advance.

EDIT:

Answer 1

In the formula where it does not work:

=LOOKUP(T3,'Electrode Clalc'!P3:P37,'Electrode Clalc'!E3:E37)

It is because:

• the value in T3 cannot be found in P3:P37 (check for spaces before or after);
• or if it is found, on the same row of the found value in column E there is an error, thus it returns the error correctly;