Got any Excel Questions? Free Excel Help .
Would like to show you what I call: In-Cell-Lookups. These are the perfect replacement for multiple nested IF functions.
Enter 1 in cell A1
Select A1 and while holding down the Ctrl key and drag down by the Fill Handle until you reach Cell A20.
Now, in B1 add this formula;
and double click the Fill Handle so this formula is copied down to B20.
As you can see, this returns a text result of the numeric scope our numbers fall into.
Here's the details of how this works. Text quoted from Excel help
lookup_value: Required. A value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.
lookup_vector: Required. A range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values.
Important: The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.
result_vector: Required. A range that contains only one row or column. The result_vector argument must be the same size as lookup_vector.
For both lookup_vector and Result_vector we have used what is known as Array Constants
After reading the link above you should understand "Array Constants". So, as you can see our "lookup_vector" is placed in ascending order using the lowest value for each numeric scope. Our "result_vector" Array Constants correspond to our "lookup_vector" Array constants.
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 [email protected] 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