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

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