 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
| |
The Excel Vlookup formula is perhaps the most used formula used when looking up data set up in a table.
Got any Excel Questions? Excel Help & Excel Video Tutorials
SEE ALSO: Vlookup Across Worksheets | 5 Condition Vlookup |4 Condition Vlookup | 3 Condition Vlookup |2 Condition Vlookup | How to stop the #N/A! error | Lookup Any Occurrence in Any Table Column || Hlookup Formula ||Left Lookup in Excel ||Excel Lookup Functions | Multi-Table Lookup
Perhaps one of Excels most commonly needed Functions is the VLOOKUP. It is also possibly the function that most people have problems understanding. The Excel VLOOKUP function is used to look for specified data in the first column of a table of data. Once found it will return a result, on the same row, a specified number of columns from the first column. The syntax for VLOOKUP is:
=vlookup(lookup_value,table_array,col_index_num,range_lookup)
It is used in the following manner:
=VLOOKUP("Dog",$A$1:$E$1000,3,False)
Note the use of False as the optional range_lookupArgument. This tells the VLOOKUP to find an exact match and is most often needed when looking for a text match. If this is omitted, or True, you will often get unwanted results when searching for text that is in an unsorted column of data. This means that when True is used, or the range_lookup Argument is omitted, your data should be sorted (by the first column) in ascending order.
The use of True, or range_lookup Argument is omitted, is most often used when looking at numeric data that resides in the first column of your table of data.
| A | B | C | |
| 1 | Amount | Name | Age |
| 2 | $256.95 | Bill | 56 |
| 3 | $125.63 | Joe | 22 |
| 4 | $25.66 | Mary | 59 |
| 5 | $548.00 | Dave | 21 |
| 6 | $489.32 | Frank | 48 |
| 7 | $500.25 | Sue | 19 |
| 8 | $368.59 | Hillary | 15 |
| 9 | $901.56 | Kate | 25 |
| 10 | $99.95 | Aleisha | 33 |
If we were to use:
=VLOOKUP(368.59,$A$1:$C$10,2)
on the above data, we would get a result of "Mary" and NOT "Hillary" as we have omitted the range_lookup Argument AND our data is not sorted by Amount. This is because VLOOKUP looks in Column A, Starting from A1, and as soon as it encounters the amount $548.00 it drops back to the cell above because $548.00 is greater than the lookup_value of 368.59
If we sorted our data by Amount we would get the desired result. We could also get the desired result by using:
=VLOOKUP(368.59,$A$1:$C$10,2,False)
The use of False as the range_lookup Argument forces VLOOKUP to search all values and only stop when it finds an exact match. If an exact match does not exist we get the #N/A! error.
The method of sorting is best as a VLOOKUP that searches in a sorted range is MUCH faster. The effect of this can be significant if the table is large and/or you have many VLOOKUP functions.
How to stop the #N/A! error |Excel Video Tutorials
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader PackageTechnical 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