Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Conditional Formatting Rules in Excel
  • Filter Data Based on Date in Excel
  • Get column name from index in Excel Table
  • Conditional Formatting Icon Sets Examples in Excel
  • How to calculate average last N values in a table in Excel

References

  • Count rows that contain specific values in Excel
  • How to get first row number in range in Excel
  • How to get relative row numbers in a range in Excel
  • How to use Excel FORMULATEXT function
  • Get nth match with INDEX / MATCH in Excel

Data Validations

  • Excel Data validation no punctuation
  • How To Create Drop-down List in Excel
  • Excel Data validation exists in list
  • Excel Data validation number multiple 100
  • Excel Data validation only dates between

How to use Excel LOOKUP Function

by

This Excel tutorial explains how to use the LOOKUP function with syntax and examples.

Excel LOOKUP function Description

The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array.

The LOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the LOOKUP function can be entered as part of a formula in a cell of a worksheet.

Explanation: Based on the example above, the LOOKUP function would return:

=LOOKUP(10251, A1:A6, B1:B6)
Result: "Pears"

=LOOKUP(10251, A1:A6)
Result: 10251

=LOOKUP(10246, A1:A6, B1:B6)
Result: #N/A

=LOOKUP(10248, A1:A6, B1:B6)
Result: "Apples"

There are 2 different syntaxes for the LOOKUP function:

LOOKUP Function (Syntax #1)

In Syntax #1, the LOOKUP function searches for value in the lookup_range and returns the value in the result_range that is in the same position.

The syntax for the LOOKUP function in Microsoft Excel is:

LOOKUP( value, lookup_range, [result_range] )

Arguments

value
The value to search for in the lookup_range.
lookup_range
A single row or single column of data that is sorted in ascending order. The LOOKUP function searches for value in this range.
result_range
Optional. It is a single row or single column of data that is the same size as the lookup_range. The LOOKUP function searches for the value in the lookup_range and returns the value from the same position in the result_range. If this parameter is omitted, it will return the first column of data.

Returns

The LOOKUP function returns any datatype such as a string, numeric, date, etc.
If the LOOKUP function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value.
If the value is smaller than all of the values in the lookup_range, then the LOOKUP function will return #N/A.
If the values in the LOOKUP_range are not sorted in ascending order, the LOOKUP function will return the incorrect value.

LOOKUP Function (Syntax #2)

In Syntax #2, the LOOKUP function searches for the value in the first row or column of the array and returns the corresponding value in the last row or column of the array.

The syntax for the LOOKUP function in Microsoft Excel is:

LOOKUP( value, array )

Arguments

value
The value to search for in the array. The values must be in ascending order.
array
An array of values that contains both the values to search for and return.

Returns

The LOOKUP function returns any datatype such as a string, numeric, date, etc.
If the LOOKUP function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value.
If the value is smaller than all of the values in the lookup_range, then the LOOKUP function will return #N/A.
If the values in the array are not sorted in ascending order, the LOOKUP function will return the incorrect value.

Post navigation

Previous Post:

VLOOKUP without #N/A error in Excel

Next Post:

Manipulating text strings using Left, Mid, Right, Len, Substitute in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use Excel TRUE Function
  • Excel If, Nested If, And/Or Criteria Examples
  • How to return blank in place of #DIV/0! error in Excel
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • IFNA function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • How to join date and text together in Excel
  • Count times in a specific range in Excel
  • How to get Holiday Date from Year in Excel
  • Get last day of month in Excel
  • Add business days to date in Excel

Grouping

  • How to randomly assign people to groups in Excel
  • Categorize text with keywords in Excel
  • Running count group by n size in Excel
  • How to randomly assign data to groups in Excel
  • Calculate conditional mode with criteria in Excel

General

  • How to count total columns in range in Excel
  • How to get random value from list or table in Excel
  • How to get amount with percentage in Excel
  • Sum by group in Excel
  • How to calculate profit margin percentage in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning