MORE INFORMATION
You can use a combination of the INDEX and MATCH functions,
a combination of the OFFSET and MATCH functions, HLOOKUP, or VLOOKUP to provide
the same functionality as LOOKUP. None of these choices require that the lookup
table be sorted, unlike the LOOKUP function.
Using INDEX and MATCH
The following formula returns the same information that a VLOOKUP
returns without requiring the first column of the table to be sorted
=INDEX(Table_Array,MATCH(Lookup_Value,Lookup_Array,0),Col_Index_Num)
where:
Table_Array = The entire lookup table.
Lookup_Value = The value to be found in the first column of
"table_array".
Lookup_Array = The range of cells containing possible
lookup values.
Col_Index_Num = The column number in "table_array" for which
the matching value should be returned.
If none of the cells in Lookup_Array match Lookup_Value,
this formula will return #N/A.
Using OFFSET and MATCH
You can use the OFFSET function if you are unsure of the entire
address of "table_array" because it uses the MATCH function to locate the
position of "lookup_value" within "lookup_array." In the following function,
the "offset_col" argument uses the upper-left cell of the table as "top_cell"
and returns the value you want as determined by the MATCH (offset row) and
offset column:
=OFFSET("top_cell",MATCH("lookup_value","lookup_array",0),"offset_col")
Example
A1: Name B1: Dept C1: Age
A2: Henry B2: 501 C2: 28
A3: Stan B3: 201 C3: 19
A4: Mary B4: 101 C4: 22
A5: Larry B5: 301 C5: 29
Because the "Name" range is not sorted, using VLOOKUP to
find the age of Mary returns 28, the age of Henry. To find the age of Mary, use
the following:
=OFFSET(A1,MATCH("Mary",A2:A5,0),2)
The function returns 22.
HLOOKUP and VLOOKUP
If you are looking for an exact match in Microsoft Excel, the
first column of a lookup table does not have to be sorted to use the VLOOKUP
and HLOOKUP functions. To look for an exact match, specify the fourth argument
of VLOOKUP or HLOOKUP as FALSE. If you omit the fourth argument, or specify the
argument as TRUE, you must sort the first column of the table. The OFFSET
function described earlier also works in Microsoft Excel, but is not necessary.
Note Use the VLOOKUP and HLOOKUP functions instead of the array form
of LOOKUP because VLOOKUP and HLOOKUP do not require the lookup table to be
sorted if the range_lookup argument is FALSE.
For more information about using the
VLOOKUP function to find anexact match in Microsoft Excel, click the following article number to view the article in the Microsoft Knowledge Base:
181213
How to use VLOOKUP or HLOOKUP
to find an exact match