Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

Excel Lookups With Array Constants

| | Information Helpful? Why Not Donate.

 

Got any Excel Questions? Free Excel Help .

Lookups With Array Constants

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;
=LOOKUP(A1,{1,6,11,16},{"1-5","6-10","11-15","16-20"})
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

SYNTAX:=LOOKUP(lookup_value,lookup_vector,result_vector)

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

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

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