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

Data Analysis

  • Conflicting Multiple Conditional Formatting Rules in Excel
  • How To Insert and Customize Sparklines in Excel
  • How To Create Pareto Chart in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • Understanding Anova in Excel

References

  • To count total rows in a range in Excel
  • Extract all partial matches in Excel
  • How to get last row in text data in Excel
  • Extract data with helper column in Excel
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions

Data Validations

  • Excel Data validation date in specific year
  • Excel Data validation whole percentage only
  • Data validation must not exist in list
  • Excel Data validation only dates between
  • How To Create Drop-down List in Excel

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

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

  • Nested IF function example in Excel
  • IF with boolean logic in Excel
  • Not Equal To ‘<>‘ operator in Excel
  • How to use Excel FALSE Function
  • How to return blank in place of #DIV/0! error in Excel

Date Time

  • DATEDIF function: Description, Usage, Syntax, Examples and Explanation
  • Add decimal minutes to time in Excel
  • MINUTE function: Description, Usage, Syntax, Examples and Explanation
  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • SECOND function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Categorize text with keywords in Excel
  • Calculate conditional mode with criteria in Excel
  • Map inputs to arbitrary values in Excel
  • Running count group by n size in Excel
  • Group times into 3 hour buckets in Excel

General

  • Count cells that do not contain errors in Excel
  • Basic numeric sort formula in Excel
  • Subtotal invoices by age in Excel
  • Basic text sort formula in Excel
  • Sum by group in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning