EXCEL LOOKUP FUNCTION
Excel LOOKUP Function Introduction
Use this method when you need to search in a single row or column and find a value from the same location in a second row or column. This function can be used in two ways. There are two types of arrays: vectors and arrays.
Vector Form
Use this form to look for a value in a single row or column. We’ll explain further with an example.
Array Form
This version of LOOKUP is offered for compatibility with other spreadsheet applications; however, its usefulness is restricted; instead, use VLOOKUP or HLOOKUP. LOOKUP’s array form is quite similar to the HLOOKUP and VLOOKUP routines.
The distinction is that HLOOKUP looks for the value of lookup value in the first row, VLOOKUP searches in the first column, and LOOKUP searches according to the array’s dimensions. The LOOKUP function has a restriction in that it always returns the latest value in the row or column; it does not return in-between values. As a result, you must use VLOOKUP or HLOOKUP.
Description of Excel LOOKUP Function
Syntax:
LOOKUP(lookup_value, lookup_vector, [result_vector])
Parameter list:
- LOOKUP(lookup value, lookup vector, [result vector]) is the syntax.
- List of parameters: lookup value – a value found by LOOKUP in the first vector.
- It might be a number, text, logical value, or a name or reference to a value.
- If the LOOKUP function is unable to locate the lookup value, it returns the value that is less than or equal to the lookup value.
- lookup vector – a range that only comprises one row or column.
- Lookup vector values can be text, integers, or logical values.
- result vector [Optional] – a single-row or single-column range.
- The size of the result vector parameter must be the same as the size of the lookup vector argument.
- It must be of the same size.
Step By Step Guide of LOOKUP Function
Example :
Examine the sample file exceltovba.com-LOOKUP.xlsx Sheet1.
In this example, we first look for 14.19 in column A and then compare it to the number in column B in the same row. Second, we look for 15.75 in column A and, if it does not match, we match the next lower number (15.17) and return the value from column B that is in the same row. Third, we look for 17.66 in column A and, if it does not match, we match the next smaller number (16.39) and return the value from column B that is in the same row. Looks up 0 in column A and returns an error because 0 is less than the smallest number (14.14) in column A.
Key Points
- Important points to consider. The values in lookup vector must be in ascending order:…, -2, -1, 0, 1, 2,…, A- Z, FALSE, TRUE; otherwise, LOOKUP may fail to provide the right value. Lookup does not care about case; uppercase and lowercase text are interchangeable. If lookup value is less than the smallest value in lookup vector, LOOKUP returns #N/A.
Related Functions
HLOOKUP Function
HLOOKUP Function
Explore NowVLOOKUP Function
VLOOKUP Function
Explore NowINDEX Function
INDEX Function
Explore NowMATCH Function
MATCH Function
Explore NowDATE Function
DATE Function
Explore NowIF Function
IF Function
Explore NowOR Function
OR Function
Explore NowAND Function
AND Function
Explore NowRANK Function
RANK Function
Explore NowSUMIF Function
SUMIF Function
Explore NowXIRR Function
XIRR Function
Explore NowWORKDAYS Function
WORKDAYS Function
Explore Now
