Back to Excel Newsletter Archives

# OzGrid Excel and VBA Newsletter September 2008

 Special! Buy ANY product,email your payment ID to us and tell us which FREEBIE you would prefer.Complete Excel Training Course orExcel Add-ins Collection

## Format Cell To Include Symbols: Superscript & Subscript in Custom Formats

Also seeCustom Formats |Convert a Numeric Value into English Words | Convert a Currency or Value into English Words

One of the most important things to do in Excel is to keep numbers as numbers and text as text. Sadly, many end up mixing the 2 and find it hard or impossible to do further downstream calculations on their numbers as they have either formatted the cell as Text or mixed text and numbers in the same cells. SeeExcel Best Practice for more common mistakes.

Using Excel's Custom Formats we eliminate the need to format numeric cells as Text and mix text and numbers in the same cell. For example, suppose you need to show Meters2 in certain cells. This is quite easy to do with a custom number format.

1. Select any empty cell and go Insert>Symbol and insert the symbol required. Superscript 2 in this example.

2. Now, with the cell containing the symbol selected, click in the Formula Bar, highlight the symbol and copy (Ctrl+c)

3. Now select the range that should show Meters2 after their entered numbers and go Format>Cells - Custom.

4. Use either General "Meters²";-General "Meters²" or 0.00 "Meters²";-0.00 "Meters²" or another variation by pasting in the symbol (Ctrl+v).

Note the use of - in second format segment. Excel see a cells format as having four Sections. These are, from left to right Positive numbers, Negative Numbers, Zero Values and Text values. Each of these Sections are separated by a semi colon (;). If you create a custom number format you do not have to specify all four sections. By this I mean, if you included only two sections, the first section would be used for both positive numbers and zero values, while the second section would be used for negative numbers. If you only used onesection, all number types would use that one format. Text is only affected by custom formats when we use all four sections, the text would use the last section. See Custom Formats for full details and many more cool number formats.

## Multiple Condition/Criteria VLOOKUP

This Custom Function  can use the 1st 2 Columns of a table as matching criteria and return the nth column row to the right of the matching row range.

`Function Two_Con_Vlookup(Table_Range As Range, Return_Col As Long, Col1_Fnd, Col2_Fnd)''EXCEL 2003 OR ABOVE''WRITTEN BY OZGRID.COMDim rCheck As Range, bFound As Boolean, lLoop As Long        On Error Resume Next    Set rCheck = Table_Range.Columns(1).Cells(1, 1)    With WorksheetFunction        For lLoop = 1 To .CountIf(Table_Range.Columns(1), Col1_Fnd)           Set rCheck = Table_Range.Columns(1).Find(Col1_Fnd, rCheck, xlValues, xlWhole, xlNext, xlRows, False)           If UCase(rCheck(1, 2)) = UCase(Col2_Fnd) Then                bFound = True                Exit For            End If        Next lLoop    End With    If bFound = True Then        Two_Con_Vlookup = rCheck(1, Return_Col)    Else     Two_Con_Vlookup = "#N/A"    End IfEnd Function`

OR

`Function Two_Con_Vlookup(Table_Range As Range, Return_Col As Long, Col1_Fnd, Col2_Fnd)''PRE EXCEL 2003''WRITTEN BY OZGRID.COMDim rCheck As Range, bFound As Boolean, lLoop As Long, lRow As Long        On Error Resume Next    Set rCheck = Table_Range.Columns(1).Cells(1, 1)    With WorksheetFunction        For lLoop = 1 To .CountIf(Table_Range.Columns(1), Col1_Fnd)            lRow = .Match(Col1_Fnd, Table_Range.Columns(1).Range("A" & lRow + 1 & ":A65536"), 0) + lRow            Set rCheck = Table_Range.Columns(1).Range("A" & lRow)            If UCase(rCheck(1, 2)) = UCase(Col2_Fnd) Then                bFound = True                Exit For            End If        Next lLoop    End With    If bFound = True Then        Two_Con_Vlookup = rCheck(1, Return_Col)    Else     Two_Con_Vlookup = "#N/A"    End IfEnd Function`

Use like;
=Two_Con_Vlookup(\$A\$1:\$H\$20,6,"Apr",4)
in any cell other than \$A\$1:\$H\$20

Where \$A\$1:\$H\$20 is the table range

6 is the nth column to return the matching row from.

"Apr" and 4 are the 2 conditions to match in the 1st 2 Columns

This Custom Function  can use the 1st 3 Columns of a table as matching criteria and return the nth column row to the right of the matching row range.

`Function Three_Con_Vlookup(Table_Range As Range, Return_Col As Long, Col1_Fnd, Col2_Fnd, Col3_Fnd)Dim rCheck As Range, bFound As Boolean, lLoop As Long        On Error Resume Next    Set rCheck = Table_Range.Columns(1).Cells(1, 1)    With WorksheetFunction        For lLoop = 1 To .CountIf(Table_Range.Columns(1), Col1_Fnd)           Set rCheck = Table_Range.Columns(1).Find(Col1_Fnd, rCheck, xlValues, xlWhole, xlNext, xlRows, False)           If UCase(rCheck(1, 2)) = UCase(Col2_Fnd) And _                UCase(rCheck(1, 3)) = UCase(Col3_Fnd) Then                bFound = True                Exit For            End If        Next lLoop    End With    If bFound = True Then        Three_Con_Vlookup = rCheck(1, Return_Col)    Else        Three_Con_Vlookup = "#N/A"    End IfEnd Function`

OR

`Function Three_Con_Vlookup(Table_Range As Range, Return_Col As Long, Col1_Fnd, Col2_Fnd, Col3_Fnd)''PRE EXCEL 2003''WRITTEN BY OZGRID.COMDim rCheck As Range, bFound As Boolean, lLoop As Long, lRow As Long        On Error Resume Next    Set rCheck = Table_Range.Columns(1).Cells(1, 1)    With WorksheetFunction        For lLoop = 1 To .CountIf(Table_Range.Columns(1), Col1_Fnd)            lRow = .Match(Col1_Fnd, Table_Range.Columns(1).Range("A" & lRow + 1 & ":A65536"), 0) + lRow            Set rCheck = Table_Range.Columns(1).Range("A" & lRow)            If UCase(rCheck(1, 2)) = UCase(Col2_Fnd) And _                UCase(rCheck(1, 3)) = UCase(Col3_Fnd) Then                bFound = True                Exit For            End If        Next lLoop    End With    If bFound = True Then        Three_Con_Vlookup = rCheck(1, Return_Col)    Else        Three_Con_Vlookup = "#N/A"    End IfEnd Function`

Use like;
=Three_Con_Vlookup(\$A\$1:\$H\$20,6,"Apr",4,"Thu")
in any cell other than \$A\$1:\$H\$20

Where \$A\$1:\$H\$20 is the table range

6 is the nth column to return the matching row from.

"Apr",4 and "Thu" are the 3 conditions to match in the 1st 3 Columns

This Custom Function  can use the 1st 4 Columns of a table as matching criteria and return the nth column row to the right of the matching row range.

`Function Four_Con_Vlookup(Table_Range As Range, Return_Col As Long, Col1_Fnd, Col2_Fnd, Col3_Fnd, Col4_Fnd)''EXCEL 2003 OR ABOVE''WRITTEN BY OZGRID.COMDim rCheck As Range, bFound As Boolean, lLoop As Long        On Error Resume Next    Set rCheck = Table_Range.Columns(1).Cells(1, 1)    With WorksheetFunction        For lLoop = 1 To .CountIf(Table_Range.Columns(1), Col1_Fnd)           Set rCheck = Table_Range.Columns(1).Find(Col1_Fnd, rCheck, xlValues, xlWhole, xlNext, xlRows, False)           If UCase(rCheck(1, 2)) = UCase(Col2_Fnd) And _                UCase(rCheck(1, 3)) = UCase(Col3_Fnd) And _                UCase(rCheck(1, 4)) = UCase(Col4_Fnd) Then                bFound = True                Exit For            End If        Next lLoop    End With    If bFound = True Then        Four_Con_Vlookup = rCheck(1, Return_Col)    Else        Four_Con_Vlookup = "#N/A"    End IfEnd Function`

OR

`Function Four_Con_Vlookup(Table_Range As Range, Return_Col As Long, Col1_Fnd, Col2_Fnd, Col3_Fnd, Col4_Fnd)''PRE EXCEL 2003''WRITTEN BY OZGRID.COMDim rCheck As Range, bFound As Boolean, lLoop As Long, lRow As Long        On Error Resume Next    Set rCheck = Table_Range.Columns(1).Cells(1, 1)    With WorksheetFunction        For lLoop = 1 To .CountIf(Table_Range.Columns(1), Col1_Fnd)            lRow = .Match(Col1_Fnd, Table_Range.Columns(1).Range("A" & lRow + 1 & ":A65536"), 0) + lRow            Set rCheck = Table_Range.Columns(1).Range("A" & lRow)            If UCase(rCheck(1, 2)) = UCase(Col2_Fnd) And _                UCase(rCheck(1, 3)) = UCase(Col3_Fnd) And _                UCase(rCheck(1, 4)) = UCase(Col4_Fnd) Then                bFound = True                Exit For            End If        Next lLoop    End With    If bFound = True Then        Four_Con_Vlookup = rCheck(1, Return_Col)    Else        Four_Con_Vlookup = "#N/A"    End IfEnd Function`

Use like;
=Four_Con_Vlookup(\$A\$1:\$H\$20,6,"Apr",4,"Thu","Larry")
in any cell other than \$A\$1:\$H\$20

Where \$A\$1:\$H\$20 is the table range

6 is the nth column to return the matching row from.

"Apr",4,"Thu" and "Larry" are the 4 conditions to match is the 1st 4 Column

This Custom Function  can use the 1st 5 Columns of a table as matching criteria and return the nth column row to the right of the matching row range.

`Function Five_Con_Vlookup(Table_Range As Range, Return_Col As Long, Col1_Fnd, Col2_Fnd, Col3_Fnd, Col4_Fnd, Col5_Fnd)''EXCEL 2003 OR ABOVE''WRITTEN BY OZGRID.COMDim rCheck As Range, bFound As Boolean, lLoop As Long        On Error Resume Next    Set rCheck = Table_Range.Columns(1).Cells(1, 1)    With WorksheetFunction        For lLoop = 1 To .CountIf(Table_Range.Columns(1), Col1_Fnd)           Set rCheck = Table_Range.Columns(1).Find(Col1_Fnd, rCheck, xlValues, xlWhole, xlNext, xlRows, False)           If UCase(rCheck(1, 2)) = UCase(Col2_Fnd) And _                UCase(rCheck(1, 3)) = UCase(Col3_Fnd) And _                UCase(rCheck(1, 4)) = UCase(Col4_Fnd) And _                UCase(rCheck(1, 5)) = UCase(Col5_Fnd) Then                bFound = True                Exit For            End If        Next lLoop    End With    If bFound = True Then        Five_Con_Vlookup = rCheck(1, Return_Col)    Else        Five_Con_Vlookup = "#N/A"    End IfEnd Function`

OR

`Function Five_Con_Vlookup(Table_Range As Range, Return_Col As Long, Col1_Fnd, Col2_Fnd, Col3_Fnd, Col4_Fnd, Col5_Fnd)''PRE EXCEL 2003''WRITTEN BY OZGRID.COMDim rCheck As Range, bFound As Boolean, lLoop As Long, lRow As Long        On Error Resume Next    With WorksheetFunction        For lLoop = 1 To .CountIf(Table_Range.Columns(1), Col1_Fnd)           lRow = .Match(Col1_Fnd, Table_Range.Columns(1).Range("A" & lRow + 1 & ":A65536"), 0) + lRow           Set rCheck = Table_Range.Columns(1).Range("A" & lRow)           If UCase(rCheck(1, 2)) = UCase(Col2_Fnd) And _                UCase(rCheck(1, 3)) = UCase(Col3_Fnd) And _                UCase(rCheck(1, 4)) = UCase(Col4_Fnd) And _                UCase(rCheck(1, 5)) = UCase(Col5_Fnd) Then                bFound = True                Exit For            End If        Next lLoop    End With    If bFound = True Then        Five_Con_Vlookup = rCheck(1, Return_Col)    Else        Five_Con_Vlookup = "#N/A"    End IfEnd Function`

Use like;
=Five_Con_Vlookup(\$A\$1:\$H\$20,6,"Apr",4,"Thu","Larry",55)
in any cell other than \$A\$1:\$H\$20

Where \$A\$1:\$H\$20 is the table range

6 is the nth column to return the matching row from.

"Apr",4,"Thu","Larry" and 55 are the 5 conditions to match is the 1st 5 Columns

Got any Questions?Excel Help

Instant Download and Money Back Guarantee on Most Software

Search Tips

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

FREE Excel Help