Go Back

Pro Tips on Using the LOOKUP Function in Excel

We can use the Excel LOOKUP function to get an approximate match of the lookup value in a one-column or one-row range and return the corresponding value from another one-column or one-row range. The steps below will walk through the process.

Figure 1- How to Use the LOOKUP Function in Excel

Syntax

=LOOKUP(lookup_value, lookup_vector, [result_vector])

  • Lookup_value: This is the value to search for
  • Lookup_vector: This is the one-row, or one-column range to search for the lookup_value
  • Result_vector – [optional]: The one-row, or one-column range of results

Formula

Remarks: =LOOKUP(E4,B4:B8,C4:C8)

Match: =LOOKUP(E4,B4:B8)

Setting up the Data

We will use the LOOKUP FUNCTION to lookup the range and obtain the Remarks for a Grade of 80 and the Match in figure 2.

 

  • The Grades will be entered into Column B
  • Column C contains the Remarks
  • The results of the LOOKUP FUNCTION will be returned in Column E

 

Figure 2 – Setting up the Data

LOOKUP Remarks

  • We will click on Cell E5
  • We will insert the formula below into Cell E5
    =LOOKUP(E4,B4:B8,C4:C8)
  • We will press the enter key

Figure 3- Lookup result for Remarks

LOOKUP Match

The lookup function can return the value of a cell once it has located its position. However, when it doesn’t find an exact match for the lookup value, the LOOKUP FUNCTION returns the next smaller number as the result.

  • We will click on Cell E6
  • We will insert the formula below into Cell E6
    =LOOKUP(E4,B4:B8)
  • We will press the enter key

Figure 4- Lookup result for Grade

Note

  • Lookup function has the vector and array form
  • This example describes the vector form of Lookup
  • The LOOKUP function assumes that the lookup_vector is sorted in ascending order
  • If the lookup_value is greater than every value in lookup_vector, the LOOKUP function matches the last value

If the lookup_value is less than all (i.e. the first) value in lookup_vector, the LOOKUP function returns the #N/A error. “The result_vector must be the same size as the lookup_vector.” The LOOKUP function isn’t case-sensitive.

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment

avatar