Excel has several powerful lookup functions that allow you to search for specific values within a range of cells and return corresponding information. Remember, the choice of lookup function depends on the specific requirements of your task. By understanding and utilizing these functions effectively, you can streamline your data analysis and make your spreadsheets more efficient.
| Function | Version | Purpose | Arguments |
|---|---|---|---|
| ADDRESS | Excel 2003 | Create a cell address from a row and column number | row_num col_num abs_num a1 sheet |
| AREAS | Excel 2003 | Get the number of areas in a reference. | reference |
| CHOOSE | Excel 2003 | Get a value from a list based on position | index_num value1 value2 … |
| COLUMN | Excel 2003 | Get the column number of a reference. | reference |
| COLUMNS | Excel 2003 | Get the number of columns in an array or reference. | array |
| FIELDVALUE | Excel 365 | Extract field value from a data type | value field_name |
| FORMULATEXT | Excel 2013 | Get the formula in a cell | reference |
| GETPIVOTDATA | Excel 2003 | Retrieve data from a pivot table in a formula | data_field pivot_table field1, item1 … |
| HLOOKUP | Excel 2003 | Look up a value in a table arranged horizontally | lookup_value table_array row_index range_lookup |
| HYPERLINK | Excel 2003 | Create a clickable link. | link_location friendly_name |
| INDEX | Excel 2003 | Get a value in a list or table based on location | array row_num col_num area_num |
| INDIRECT | Excel 2003 | Create a reference from text | ref_text a1 |
| LOOKUP | Excel 2003 | Look up a value in a one-column range | lookup_value lookup_vector result_vector |
| MATCH | Excel 2003 | Get the position of an item in an array | lookup_value lookup_array match_type |
| OFFSET | Excel 2003 | Create a reference offset from given starting point | reference rows cols height width |
| ROW | Excel 2003 | Get the row number of a reference | reference |
| ROWS | Excel 2003 | Get the number of rows in an array or reference. | array |
| TRANSPOSE | Excel 2003 | Flip the orientation of a range of cells | array |
| VLOOKUP | Excel 2003 | Look up and retrieve a value in a table | lookup_value table_array column_index_num range_lookup |