Lookup Formulas in Excel
Why search for data manually when Excel will do all of the heavy lifting for you. Let’s walk through how to use lookup formulas in Excel and quickly analyze data sets.
What is a Lookup?
Lookup formulas allow you to pull values from a data set by looking for the same position in a certain row or column. This is helpful for simple analysis. For instance, you can use with data sets like P&Ls or other financial statements where the column and row headers are unique and clearly defined.
HLOOKUP Formula
The HLOOKUP Formula searches horizontally (Horizontal Lookup) across columns for a value. Once found, it then returns the value from the specified row.
The formula is =HLOOKUP(lookup_value, table_array, row_index_number, [range_lookup]). Lookup_value is the value from the table you want to search for. It can be numerical or a text value and entered as a hardcoded value or a reference. After that, table_array is the data set you want to search through. The lookup_value must be in the first row.
Row_index_number is the number of rows you want to count down in the table. Excel will return the value in that cell. Lastly, Range_lookup is optional and determines whether the closest match or the perfect match will be found. You should enter range_lookup as True or False. I recommend using False consistently.
In this example, we want to return the wage expense for two different months (horizontal axis). Make sure to count the lookup row!
VLOOKUP Formula
The VLOOKUP Formula searches vertically (Vertical Lookup) down rows for a value and then returns the value from a specified column. Lookup_value is the value from the table you want to search for. It can be numerical or a text value and entered as a hardcoded value or a reference. After that, table_array is the data set you want to search through. The lookup_value must be in the first row.
Col_index_number is the number of columns you want to count across in the table. Excel will return the value in that cell. Lastly, Range_lookup is optional and determines whether the closest match or the perfect match will be found. You should enter range_lookup as True or False. I recommend using False consistently.
The formula is =VLOOKUP(lookup_value, table_array, col_index_number, [range_lookup])
In this example, we want to return two accounts for a single month (vertical axis). Make sure to count the lookup row!
Alternative Formula
Index Match is a more dynamic alternative to lookup formulas. This formula can search along both the row and column axis. You can find more information here: How to Use Index Match in Excel
Have any questions on lookup formulas in Excel? Are there other topics you would like us to cover? Leave a comment below and let us know! And make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.
