LOOKUP Function

The lookup_value is smaller than the smallest value in the lookup_vector, or the lookup_vector is not sorted in ascending order.

1. Verify that lookup_vector is sorted in ascending order (smallest to largest) 2. Check if your lookup_value is within the range of values in lookup_vector 3. Ensure there are no blank cells at the beginning of lookup_vector 4. If data cannot be sorted, consider using INDEX/MATCH with exact match instead

Always sort your lookup_vector in ascending order before using LOOKUP. Use data validation to ensure new entries maintain sort order.

The lookup_vector or result_vector references cells or ranges that have been deleted, or the two vectors are different sizes.

1. Verify that both lookup_vector and result_vector still exist and haven't been deleted 2. Ensure lookup_vector and result_vector are the same size (same number of rows or columns) 3. Check that both ranges are either both rows or both columns (not mixed) 4. Use named ranges to prevent reference errors when rows/columns are inserted or deleted 5. Rebuild the formula if references cannot be recovered

Use structured references or named ranges instead of cell references. Before deleting rows or columns, check for LOOKUP formulas that might reference them.

The lookup_vector contains non-numeric values when searching for a number, or contains mixed data types that prevent proper sorting.

1. Ensure data types are consistent in lookup_vector (all numbers or all text, not mixed) 2. Use VALUE() function to convert text numbers to actual numbers: =LOOKUP(A2, VALUE(B:B), C:C) 3. Remove or replace any error values in the lookup_vector 4. Check for hidden characters or spaces that might affect data type recognition 5. Verify that lookup_value matches the data type in lookup_vector

Maintain consistent data types in your lookup ranges. Use data validation to restrict input types. Apply proper number or text formatting to columns.

The lookup_vector is not sorted in ascending order, or LOOKUP performed an approximate match when exact match was expected.

1. Sort lookup_vector in ascending order (A-Z for text, smallest to largest for numbers) 2. Remember that LOOKUP always does approximate matching - it finds the largest value ≤ lookup_value 3. If you need exact matches, use VLOOKUP with FALSE or INDEX/MATCH instead 4. Check for duplicate values in lookup_vector that might cause ambiguous results 5. Verify there are no blank cells in the middle of your lookup_vector

Always sort data before using LOOKUP. Document that LOOKUP is being used for approximate matches. Consider VLOOKUP or XLOOKUP if exact matches are required.