Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
Lookup tables in excel are a named tables which are used with vlookup function to find any data, when we have a large amount of data and we do not know where to look we can select the table and give it a name and while using the vlookup function instead of giving the reference we can type the name of the table as a reference to look up the value, such table is known as lookup table in excel.
Lookup Table in Excel – Table of Contents
- Create a Lookup Table Using VLOOKUP Function
- Use LOOKUP Function to Create a LOOKUP Table in Excel
- Use INDEX + MATCH Function to Create a LOOKUP Table in Excel
How to Create a Lookup Table in Excel?
Lookup functions are lifesavers in excel. Based on the available value or lookup value we can fetch the other data associated with it in the different data table. In excel VLOOKUP is the most commonly used lookup function.
In this article, we will discuss some of the important lookup function in excel and also how to create a lookup table in excel. Important lookup functions are VLOOKUP & HLOOKUP, V stands for Vertical Lookup and H stands for Horizontal Lookup. We have the function called LOOKUP as well to look for the data in the table.
Using these lookup functions we can fetch the available data’s other information form different worksheet as well as from different workbooks.
#1 – Create a Lookup Table Using VLOOKUP Function
As I told VLOOKUP is the traditional lookup function all most all the users use regularly. We will show you how to look for values using this lookup function.
- Lookup Value is nothing but the available value. Based on this value we are trying to fetch the data from the other table.
- Table Array is simply the main table where all the information resides.
- Col Index Num is the nothing but from which column of the table array we want the data. We need to mention the column number here.
- Range Lookup is nothing but whether you are looking for an exact match or an approximate match. If you are looking for the exact match then FALSE or 0 is the argument, if you are looking for the approximate match then TRUE or 1 is the argument.
Example of VLOOKUP Function: Assume below is the data you have of product sales and their sales amount.
Now in the cell D2 you one product id and using this product id you have to fetch the sales value using VLOOKUP.
Step 1: Apply the VLOOKUP function and open the formula first.
Step 2: First argument is LOOKUP Value. Lookup value is our base or available value. So select the cell D2 as the reference.
Step 3: Next is the table array, this is nothing but our main table where all the data resides. So select the table array as A2 to B11.
Step 4: Now press the F4 function key to make it as an absolute excel reference. It will insert the dollar symbol to the selected cell.
Step 5: Next argument is column index number, from the selected table from which column actually you are looking for the data. In this case, we have selected two columns and we need the data from the 2nd column, so mention 2 as the argument.
Step 6: Now the final argument is range lookup i.e. type of lookup. Since we are looking at exact match select FALSE or enter zero as the argument.
Step 7: Close the bracket and hit the enter key. We should have the sales value for the product id Prd 5.
Step 8: What if we want the sales data for the product if Prd6. Off course we can directly enter but this is not the right approach to do. Rather we can create the drop-down list and allow the user to select from the drop-down list. Press ALT + A + V + V in the cell D2, this is the shortcut key which is the shortcut key to create data validation in excel.
Step 9: Select LIST from Allow: drop down.
Step 10: In the SOURCE: select the Product ID list from A2 to A11.
Step 11: Click on OK. We have all the list of products in the cell D2 now.
#2 – Use LOOKUP Function to Create a LOOKUP Table in Excel
Instead of VLOOKUP, we can also use the LOOKUP function as an alternative. Let’s look at the formula of the LOOKUP function.
- Lookup Value is base value or available value.
- Lookup Vector is nothing but lookup value column in the main table.
- Result Vector is nothing but requires a column in the main table.
Let’s apply the formula to understand the logic of the LOOKUP function.
Step 1: Open lookup function now.
Step 2: Lookup value is Product Id so select D2 cell.
Step 3: Lookup vector is nothing but Product Id column in the main table. So select A1 to A11 as the range.
Step 4: Next up results vector, this is nothing but from which column we need the data to be fetched. In this case from B1 to B11, we want the data to be fetched.
Step 5: Close the bracket and hot enter to close the formula. We should have sales value for the selected product id.
Step 6: Change the Product ID to see a different result.
#3 – Use INDEX + MATCH Function
Step 1: Open INDEX formula first.
Step 2: For the first argument select result column in the main table.
Step 3: In order to get the row number we need to apply the MATCH function. Refer below image for MATCH function.
Step 4: Close the bracket and close the formula. We will have results.
Things to Remember
- Lookup should be the same as in the main table in excel.
- VLOOKUP works from left to right not from right to left.
- In LOOKUP function we just need to select the result column, need not mention the column index number, unlike VLOOKUP.
This has been a guide to LOOKUP Table in Excel. Here we discuss How to Create LOOKUP Table in Excel using VLOOKUP, Index and Match formula along with practical examples and downloadable excel template. You may learn more about excel from the following articles –