# 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