Home › Functions › Lookup & Reference › =LOOKUP()
Lookup & Reference All versions ◉ 24 views

=LOOKUP()

How to use the LOOKUP function in Excel to search in a single row or column and find a value from the same position in a second row or column.

Applies To: Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for MacExcel 2019Excel 2016
=LOOKUP(lookup_value, lookup_vector, [result_vector])
Hover argument names in the table for details.
Output typeMixed
Popularity40 / 100
VolatileNo
Dynamic ArrayNo
Download sample .xlsx ▦ Try in Playground Dedicated file for this function — safe & lightweight · sample data + live formulas
Share:

⬇ Download Sample Workbook

A dedicated .xlsx for =LOOKUP() with Persian sample data & live formulas — open in Excel and practice.

  • Sheet “Sample” — data table + formula in place
  • Sheet “Guide” — syntax, arguments & tips from this page
  • Instant & secure (token + rate limit)
Download LOOKUP.xlsx ~ 8–12 KB · No macros

⇪ Share

Send this function to friends

Quiz this function

%d questions — syntax, example & errors — test yourself!

◧ Arguments

ArgumentTypeRequiredDescription
lookup_value Any type Required A value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.
lookup_vector Range Required A range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values. Important The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.
result_vector Range Optional A range that contains only one row or column. The result_vector argument must be the same size as lookup_vector. It has to be the same size.
array Logical Required A range of cells that contains text, numbers, or logical values that you want to compare with lookup_value. The array form of LOOKUP is very similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for the value of lookup_value in the first row, VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of array. If array covers an area that is wider than it is tall (more columns than rows), LOOKUP searches for the value of lookup_value in the first row. If an array is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column. With the HLOOKUP and VLOOKUP functions, you can index down or across, but LOOKUP always selects the last value in the row or column. Important The values in array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

▣ Examples

Example 1 =LOOKUP(4.19, A2:A6, B2:B6)
Example 2 =LOOKUP(5.75, A2:A6, B2:B6)
Example 3 =LOOKUP(7.66, A2:A6, B2:B6)
Example 4 =LOOKUP(0, A2:A6, B2:B6)

§ Remarks

  • If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.
  • If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns the #N/A error value.

✦ Tips

  • Consider one of the newer lookup functions, depending on which version you are using. Use VLOOKUP to search one row or column, or to search multiple rows and columns (like a table). It's a much improved version of LOOKUP. Watch this video about how to use VLOOKUP. If you are using Microsoft 365, use XLOOKUP - it's not only faster, it also lets you search in any direction (up, down, left, right).
  • We strongly recommend using VLOOKUP or HLOOKUP instead of the array form. See this video about VLOOKUP; it provides examples. The array form of LOOKUP is provided for compatibility with other spreadsheet programs, but its functionality is limited.
  • If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.

⚠ Common Errors

#N/A If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns the #N/A error value.
Fix: Check the lookup value and the first value in the range

⇄ Related Functions

=HLOOKUP() Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find. The H in HLOOKUP stands for "Horizontal." =VLOOKUP() Learn how to use function VLOOKUP in Excel to find data in a table or range by row. Our step-by-step guide makes vlookup in excel easy and efficient. =XLOOKUP() Use the XLOOKUP function when you need to find things in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on.

💬 Comments & Q&A 0 approved

🔒 Your comment will be published after review.

Share your thoughts — visible after admin approval

No comments yet — be the first!

✍️ Add comment / question

captcha
🔒 Your comment will be published after review.