 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
| |
The Excel Hlookup formula is perhaps one of the most used formula used when looking up data set up in a table.
Perhaps one of Excels most commonly needed Functions is the Hlookup.
The Excel Hlookup function is used to look for specified data in the first row of a table of data. Once found it will return a result, from the same column, a specified number of rows down from the first row. The syntax for Hlookup is:
=Hlookup(lookup_value,table_array,row_index_num,range_lookup)
It is used in the following manner:
=Hlookup("Dog",$A$1:$E$1000,3,False)
Note the use of False as the optional range_lookupArgument. This tells the Hlookup to find an exact match and is most often needed when looking for a text match. If this is omitted, or True, you will may get unwanted results when searching for text that is in an unsorted row of data. This means that when True is used, or the range_lookup Argument is omitted, your data should be sorted (by the first row) 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 row of your table of data.
HLOOKUP Example
| 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:
=HLOOKUP("Name",$A$1:$C$10,2,False)
The use of False as the range_lookup Argument forces Hlookup to search all values on row 1 and only stop when it finds an exact match. If an exact match does not exist we get the #N/A! error.
How to stop the #N/A! error when using Hlookup
One very common question asked by Excel users is "How can I stop Hlookup returning #N/A! when it cannot find my data?". There are a few ways this can be done, unfortunately the most popular way is also the least efficient. This is to use the ISNA function as shown below:
=IF(ISNA(Hlookup("Accounts",$A$1:$F$1000,2,False)),"",Hlookup("Accounts",$A$1:$F$1000,2,False))
The use of the ISNA function like this will force Excel to perform the Hlookup twice if the value does exist, which is most often the case. This can become a problem by slowing down Excel's recalculation time. A slightly better options is:
=IF(COUNTIF($A$1:$F$1,"Accounts"),Hlookup("Accounts",$A$1:$F$10000,2,False),"")
See Lookup Functions here for more ways.
SEE ALSO: Left Lookup in Excel | Excel Lookup Assistant || Excel Lookup Functions , How to Find the nth Occurrence || Excel Add-ins Over 200!
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. 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