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
| 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). |
| XLOOKUP | Returns the value in the same row after finding a matching value in any column. |
| XMATCH | Returns the position of a value in a list, table or cell range. |
| 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). |
| XLOOKUP Returns the value in the same row after finding a matching value in any column. |
| XMATCH Returns the position of a value in a list, table or cell range. |
XLOOKUP Examples
| XLOOKUP | Returns the exact match |
| XLOOKUP Returns the exact match |
VLOOKUP Examples
| 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) |
| VLOOKUP | 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 |
| 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) |
| VLOOKUP 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
| 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) |
| HLOOKUP | 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 |
| 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) |
| HLOOKUP 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
| 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 |
| 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 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 |
| 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 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 |
| 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 |
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext