Word, Excel, and my OS are all (or at least claim to be) up to date - Version 16.30 of Word and Excel, Mac OS 10.15.1 Catalina. I see that other people have experienced a different issue with Word for Mac, but this appears to be inconveniently unrelated except for the keywords involved. Excel 2016 Tutorial for beginnersThis is a Microsoft Excel 2016 Full Tutorial for beginners & intermediate users.In this crash course Excel video tutorial yo.
Yesterday, in the 30XL30D challenge, we had some fun with the REPT function, and created in-cell charts and a tally sheet. Now it's Monday, and time to put those thinking caps back on!
For day 16 in the challenge, we'll examine the LOOKUP function. It's a close friend of VLOOKUP and HLOOKUP, but works in a slightly different way.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let's take a look at the LOOKUP information and examples, and if you have other tips or examples, please share them in the comments.
Function 16: LOOKUP
The LOOKUP function returns a value from a one-row or one-column range, or from an array, which can have multiple rows and columns.
How Could You Use LOOKUP?
The LOOKUP function can return a result, based on a lookup value, such as:
- Find last number in a column
- Find latest month with negative amount
- Convert student percentages to letter grades
LOOKUP Syntax
The LOOKUP function has two syntax forms -- Vector and Array. With Vector form, it looks for a value in a specified column or row, and with Array form, it looks in the first row or column of an array.
The Vector form has the following syntax:
- LOOKUP(lookup_value,lookup_vector,result_vector)
- lookup_value can be text, number, logical value, a name or a reference
- lookup_vector is a range with only one row or one column
- result_vector is a range with only one row or one column
- lookup_vector and result_vector must be the same size
The Array form has the following syntax:
- LOOKUP(lookup_value,array)
- lookup_value can be text, number, logical value, a name or a reference
- searches based on the array dimensions:
- if there are more columns than rows, it searches in the first row
- if equal number, or more rows, it searches first column
- returns value from same position in last row/column
LOOKUP Traps
- The LOOKUP function doesn't have an option for Exact Match, which both VLOOKUP and HLOOKUP have. If the lookup value isn't found, it matches the largest value that is less than the lookup value.
- The lookup array or vector must be sorted in ascending order, or the result might be incorrect.
- If the first value in the lookup array/vector is bigger than the lookup value, the result is an #N/A error.
Example 1: Find Last Number in Column
In the Array form, you can use the LOOKUP function to find the last number in a column.
The specifications in Excel's Help list 9.99999999999999E+307 as the largest number allowed to be typed into a cell. In this formula, that number is entered as the lookup value. Assuming that large number won't be found, the last number in column D is returned.
In this example, the numbers in column D do not have to be sorted, and there are text entries included in the column.
=LOOKUP(9.99999999999999E+307,D:D)
Example 2: Find Latest Month With Negative Amount
Excel 16 30 Gauge
This example uses LOOKUP in its Vector form, with sales amounts in column D, and month names in column E. Things didn't go well for a few months, and there are negative amounts in the sales column.
To find the last month with a negative amount, this LOOKUP formula tests each sales amount to see if it's less than zero. Then, 1 is divided by that result, and returns either a 1 or a #DIV/0! error.
The lookup value is 2, which won't be found, so the last 1 is used, to return the month name from column E.
=LOOKUP(2,1/(D2:D8<0),E2:E8)
Example 3: Convert student percentages to letter grades
Just as you did with the VLOOKUP formula, you can use LOOKUP, in its Vector form, to find the letter grade for a student's percentage score. With LOOKUP, the percentage do not have to be in the first column of the lookup table -- you can specify any column.
Excel 16001
Here, the scores are in column D, sorted in ascending order, and letter grades are in column C, to the left of the lookup column.
=LOOKUP(C10,D4:D8,C4:C8)
Download the LOOKUP Function File
To see the formulas used in today's examples, you can download the LOOKUP function sample workbook.
The file is zipped, and is in Excel xlsx file format -- no macros.
Excel Version 16.30
Watch the LOOKUP Video
To see a demonstration of the examples in the LOOKUP function sample workbook, you can watch this short Excel video tutorial.
Excel 16005
The LOOKUP function can return a result, based on a lookup value, such as:
- Find last number in a column
- Find latest month with negative amount
- Convert student percentages to letter grades
LOOKUP Syntax
The LOOKUP function has two syntax forms -- Vector and Array. With Vector form, it looks for a value in a specified column or row, and with Array form, it looks in the first row or column of an array.
The Vector form has the following syntax:
- LOOKUP(lookup_value,lookup_vector,result_vector)
- lookup_value can be text, number, logical value, a name or a reference
- lookup_vector is a range with only one row or one column
- result_vector is a range with only one row or one column
- lookup_vector and result_vector must be the same size
The Array form has the following syntax:
- LOOKUP(lookup_value,array)
- lookup_value can be text, number, logical value, a name or a reference
- searches based on the array dimensions:
- if there are more columns than rows, it searches in the first row
- if equal number, or more rows, it searches first column
- returns value from same position in last row/column
LOOKUP Traps
- The LOOKUP function doesn't have an option for Exact Match, which both VLOOKUP and HLOOKUP have. If the lookup value isn't found, it matches the largest value that is less than the lookup value.
- The lookup array or vector must be sorted in ascending order, or the result might be incorrect.
- If the first value in the lookup array/vector is bigger than the lookup value, the result is an #N/A error.
Example 1: Find Last Number in Column
In the Array form, you can use the LOOKUP function to find the last number in a column.
The specifications in Excel's Help list 9.99999999999999E+307 as the largest number allowed to be typed into a cell. In this formula, that number is entered as the lookup value. Assuming that large number won't be found, the last number in column D is returned.
In this example, the numbers in column D do not have to be sorted, and there are text entries included in the column.
=LOOKUP(9.99999999999999E+307,D:D)
Example 2: Find Latest Month With Negative Amount
Excel 16 30 Gauge
This example uses LOOKUP in its Vector form, with sales amounts in column D, and month names in column E. Things didn't go well for a few months, and there are negative amounts in the sales column.
To find the last month with a negative amount, this LOOKUP formula tests each sales amount to see if it's less than zero. Then, 1 is divided by that result, and returns either a 1 or a #DIV/0! error.
The lookup value is 2, which won't be found, so the last 1 is used, to return the month name from column E.
=LOOKUP(2,1/(D2:D8<0),E2:E8)
Example 3: Convert student percentages to letter grades
Just as you did with the VLOOKUP formula, you can use LOOKUP, in its Vector form, to find the letter grade for a student's percentage score. With LOOKUP, the percentage do not have to be in the first column of the lookup table -- you can specify any column.
Excel 16001
Here, the scores are in column D, sorted in ascending order, and letter grades are in column C, to the left of the lookup column.
=LOOKUP(C10,D4:D8,C4:C8)
Download the LOOKUP Function File
To see the formulas used in today's examples, you can download the LOOKUP function sample workbook.
The file is zipped, and is in Excel xlsx file format -- no macros.
Excel Version 16.30
Watch the LOOKUP Video
To see a demonstration of the examples in the LOOKUP function sample workbook, you can watch this short Excel video tutorial.
Excel 16005
Excel 16 30 Round
_____________
