Ozgrid, Experts in Microsoft Excel Spreadsheets

Excel Lookup Table

| | Information Helpful? Why Not Donate.


Excel Lookup Table. Using a Lookup Table in Excel

Excel's VLookup Function. See Also: Fixed Rate Lookup || Excel VLOOKUP || Calculate Sliding Scale Tax || Left Lookup in Excel Excel

VLookup is perfect for numerical values contained in a range.  However if you tried to use VLookup with text in a table, it's use would be limited, For example surnames such as Smith, Smithson, Smithy, Smithson-Jacobs would create problems.  If you entered a surname incorrectly, VLookup will step back to the closest possible match. 

If you wish to glean information from a table that uses text, you can use VLookups optional fourth argument called match-type.  This argument forces VLookup to return #N/A if an exact match cannot be found in the first column of your table. This type of VLookup is perfect to glean information from an address list.

Let's say we wanted to find out the phone number of Smithson-Jacob.  We would use =VLOOKUP(B15,A2:E11,4,FALSE).  Telling Excel to lookup Smithson-Jacobs in the table range and return the value on the same row in the fourth column.  By using the optional fourth argument, FALSE, Excel is forced to return either an exact match (as it has done) or #N/A if it can't find an exact match. See Lookup Functions here on how to stop the #N/A in lookup formulas.

If we wanted to find the Date of Birth from within the Table we could use =VLOOKUP(B15,A2:E11,5,FALSE), which tells Excel to return the value in the fifth column on the same row.

Another way that we could write the same VLookup is by referring to the Surname within the table range like this =VLOOKUP(A6,A2:E11,5,FALSE)

See Also: Fixed Rate Lookup || Efficient Excel Spreadsheet Design || Excel VLOOKUP || Calculate Sliding Scale Tax || Left Lookup in Excel Excel

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software


Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates