 |
 |
| |
|
Back to Excel Newsletter Archives
|
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.
Select any empty cell and go Insert>Symbol and insert the symbol required. Superscript 2 in this example.
Now, with the cell containing the symbol selected, click in the Formula Bar, highlight the symbol and copy (Ctrl+c)
Now select the range that should show Meters2 after their entered numbers and go Format>Cells - Custom.
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.
SEE ALSO: VLOOKUP |Vlookup Across Worksheets | How to stop the #N/A! error | Lookup Any Occurrence in Any Table Column | Hlookup Formula |Left Lookup in Excel |Excel Lookup Functions | Multi-Table Lookup
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 FunctionUse 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 FunctionUse 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 FunctionUse 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 FunctionUse 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
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