Lookup Functions

Lookup functions search for values in a table.
Each lookup function uses a different method for searching and returning a value.


Worksheet Functions

CHOOSEReturn the value in a row (or column) based on an index number.
OFFSETReturns the value in a cell which is an offset from another cell.
MATCHReturns the position of a value in a list, table or cell range.
INDEXReturns the value from a cell range which is the intersection of a row and a column.
VLOOKUPReturns the value in the same row after finding a matching value in the first column.
HLOOKUPReturns the value in the same column after finding a matching value in the first row.
LOOKUPReturns the value in a row (or column) that matches a value in a column (or row).
CHOOSE
Return the value in a row (or column) based on an index number.
OFFSET
Returns the value in a cell which is an offset from another cell.
MATCH
Returns the position of a value in a list, table or cell range.
INDEX
Returns the value from a cell range which is the intersection of a row and a column.
VLOOKUP
Returns the value in the same row after finding a matching value in the first column.
HLOOKUP
Returns the value in the same column after finding a matching value in the first row.
LOOKUP
Returns the value in a row (or column) that matches a value in a column (or row).

Conditional Lookup

INDEX MATCHUsing the INDEX function and the MATCH function with multiple conditions.
INDEX MATCH
Using the INDEX function and the MATCH function with multiple conditions.

VLOOKUP Examples

VLOOKUPReturns the exact match
VLOOKUPReturns the exact match (item does not exist)
INDEX MATCHReturns the exact match (case sensitive)
VLOOKUPReturns the exact match (first occurrence)
VLOOKUPReturns the exact match (last occurrence, sorted)
 Returns the exact match (last occurrence, not sorted)
VLOOKUPReturns the next smallest
INDEX MATCHReturns the next largest
VLOOKUP MATCHReturns a two way lookup
VLOOKUP
Returns the exact match
VLOOKUP
Returns the exact match (item does not exist)
INDEX MATCH
Returns the exact match (case sensitive)
VLOOKUP
Returns the exact match (first occurrence)
VLOOKUP
Returns the exact match (last occurrence, sorted)

Returns the exact match (last occurrence, not sorted)
VLOOKUP
Returns the next smallest
INDEX MATCH
Returns the next largest
VLOOKUP MATCH
Returns a two way lookup

HLOOKUP Examples

HLOOKUPReturns the exact match
HLOOKUPReturns the exact match (item does not exist)
INDEX MATCHReturns the exact match (case sensitive)
HLOOKUPReturns the exact match (first occurrence)
HLOOKUPReturns the exact match (last occurrence, sorted)
 Returns the exact match (last occurrence, not sorted)
HLOOKUPReturns the next smallest
INDEX MATCHReturns the next largest
HLOOKUP MATCHReturns a two way lookup
HLOOKUP
Returns the exact match
HLOOKUP
Returns the exact match (item does not exist)
INDEX MATCH
Returns the exact match (case sensitive)
HLOOKUP
Returns the exact match (first occurrence)
HLOOKUP
Returns the exact match (last occurrence, sorted)

Returns the exact match (last occurrence, not sorted)
HLOOKUP
Returns the next smallest
INDEX MATCH
Returns the next largest
HLOOKUP MATCH
Returns a two way lookup

LOOKUP Examples

LOOKUPReturns the exact match
LOOKUPReturns the exact match (item does not exist)
INDEX MATCHReturns the exact match (case sensitive)
INDEX MATCHReturns the exact match (first occurrence)
LOOKUPReturns the exact match (last occurrence)
LOOKUPReturns the next smallest
INDEX MATCHReturns the next largest
LOOKUP
Returns the exact match
LOOKUP
Returns the exact match (item does not exist)
INDEX MATCH
Returns the exact match (case sensitive)
INDEX MATCH
Returns the exact match (first occurrence)
LOOKUP
Returns the exact match (last occurrence)
LOOKUP
Returns the next smallest
INDEX MATCH
Returns the next largest

INDEX and MATCH Examples

INDEX MATCHReturns the exact match (vlookup equivalent)
INDEX MATCHReturns the exact match (not first column)
INDEX MATCHReturns the exact match (hlookup equivalent)
INDEX MATCHReturns the exact match (not first row)
INDEX MATCH MATCHReturns a two way lookup
INDEX MATCH
Returns the exact match (vlookup equivalent)
INDEX MATCH
Returns the exact match (not first column)
INDEX MATCH
Returns the exact match (hlookup equivalent)
INDEX MATCH
Returns the exact match (not first row)
INDEX MATCH MATCH
Returns a two way lookup

OFFSET and MATCH Examples

OFFSET MATCHReturns the exact match (vlookup equivalent)
OFFSET MATCHReturns the exact match (not first column)
OFFSET MATCHReturns the exact match (hlookup equivalent)
OFFSET MATCHReturns the exact match (not first row)
OFFSET MATCH MATCHReturns a two way lookup
OFFSET MATCH
Returns the exact match (vlookup equivalent)
OFFSET MATCH
Returns the exact match (not first column)
OFFSET MATCH
Returns the exact match (hlookup equivalent)
OFFSET MATCH
Returns the exact match (not first row)
OFFSET MATCH MATCH
Returns a two way lookup


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext