Ozgrid Excel Newsletter. Excel Newsletter Archives  

Lookups With Array Constants

This month I 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 VBA: Restricting Loops

This Month I would like to show you 2 ways to restrict looping by using the COUNTIF Function with the Find Method. The 1st code uses a whole cell match, while the 2nd uses a part cell match.

The key thing to note in both codes is our use of the range variable rFound in the Find Method parameter for After: That is, After:=rFound. By using this we can move down the Column and find all matches. If we didn't use this, we always find the 1st match over and over again.

Sub RestrictLoop1WholeCellMatch()
Dim rFound As Range
Dim lLoop As Long

    With Range("A:A")
        'Set our range variable to the 1st cell in Column A
        Set rFound = .Cells(1, 1)
        
        'Use COUNTIF to restrict our looping
        For lLoop = 1 To WorksheetFunction.CountIf(.Cells, "Dave")
            'Use the Find Method and set each parameter to suit whole cell match
            Set rFound = .Find(What:="Dave", After:=rFound, LookIn:=xlValues, LookAt _
                :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                
                    With rFound
                        'Your .With code here
                    End With
        Next lLoop
    End With
End Sub

Sub RestrictLoop2PartCellMatch()
Dim rFound As Range
Dim lLoop As Long

    With Range("A:A")
        'Set our range variable to the 1st cell in Column A
        Set rFound = .Cells(1, 1)
        
        'Use COUNTIF to restrict our looping
        For lLoop = 1 To WorksheetFunction.CountIf(.Cells, "*Dave*",)
            'Use the Find Method and set each parameter to suit part cell match
            Set rFound = .Find(What:="Dave", After:=rFound, LookIn:=xlValues, LookAt _
                :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                
                    With rFound
                        'Your .With code here
                    End With
        Next lLoop
    End With
End Sub

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Become an ExcelUser Affiliate & Earn Money

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft