Microsoft Excel tips

INDEX and MATCH instead of VLOOKUP

Create a table with column headers in A1:C8.

The formula below searches for the value in C10 in the range A1:C8, and returns the value in the third column only if there is an exact match.

VLOOKUP(C10, A1:C8, 3, FALSE)

The formula below searches for the value in C10 in the range A2:A8, and, if there is an exact match, returns the corresponding value from the range C2:C8.

INDEX(C2:C8, MATCH(C10, A2:A8, 0))

The second approach is faster, as it never searches columns B and C. The second approach avoids a maintenance issue if a fourth column is introduced.

Search with two values

The VLOOKUP and SUMIF functions can be used to search using a single value, but there are times when you may wish to search using two values. A generalized example is the following array formula (use Ctrl+Shift+Enter to save this array formula):

• E20 is one value to search for, within A20 through A28
• F20 is the other value to search for, within B20 through B28
• When both match, return the value within C20 through C28

=INDEX($C$20:$C$28,MATCH(1,($A$20:$A$28=$E$20)*($B$20:$B$28=$F$20),0))

The attached spreadsheet shows two problems, and both are solved with this model.

Suppress zero

If the result appears as “0” and you would rather it was blank, you can use the following custom cell format:

General;-General;

IPv4 manipulation

Sorting by the familiar dotted decimal IPv4 format may not produce the result you want. For example, 192.168.1.12 would appear between 192.168.1.119 and 192.168.1.120.

192.168.1.11

192.168.1.119

192.168.1.12

192.168.1.120

192.168.1.121

192.168.1.13

In these cases, expressing the IPv4 number in its familiar dotted decimal notation is producing a result you did not expect. You can express the IPv4 number as a decimal number, which will sort in the manner you expect.  Assuming the IPv4 dotted decimal number is in cell A2, the following formula will produce its decimal equivalent:

=((VALUE(LEFT(A2, FIND(".", A2)-1)))*256^3)+((VALUE(MID(A2, FIND(".", A2)+1, FIND(".", A2, FIND(".", A2)+1)-FIND(".", A2)-1)))*256^2)+((VALUE(MID(A2, FIND(".", A2, FIND(".", A2)+1)+1, FIND(".", A2, FIND(".", A2, FIND(".", A2)+1)+1)-FIND(".", A2, FIND(".", A2)+1)-1)))*256)+(VALUE(RIGHT(A2, LEN(A2)-FIND(".", A2, FIND(".", A2, FIND(".", A2)+1)+1))))

Conversely, a large decimal number can be expressed in the familiar IPv4 dotted decimal notation. Assuming the decimal number is in cell A2, the following formula will produce its dotted decimal equivalent:

=IF(A2<>"", CONCATENATE(INT(A2/256^3), ".", INT(MOD(A2, (256^3))/(256^2)), ".", INT(MOD(MOD(A2, 256^3), 256^2)/256), ".", MOD(MOD(MOD(A2, 256^3), 256^2), 256)), "")

DNS lookup, PING

You can add functions named Address2Name(cell name or IP string) for DNS lookup and PingAddress(cell name or IP string) to see if the device is alive.

To perform a DNS lookup and a ping test will require macros.

To enable macros, see Create or delete a macro Record a macro steps 1 and 2:

1. If the Developer  tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.
2. To set the security level temporarily to enable all macros, do the following:
   1. On the Developer tab, in the Code group, click Macro Security.
      
   2. Under Macro Settings, click Enable all macros (not recommended, potentially dangerous code can run), and then click OK.

   Note   To help prevent potentially dangerous code from running, we recommend that you return to any one of the settings that disable all macros after you finish working with macros. For more information about how to change the settings, see Change macro security settings in Excel.

If you need assistance with creating a module, see Create Custom Functions in Excel 2007.

Create a module named Network with the following text:

Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub GetIPs()
Dim address As String
Dim row As Integer
row = 2
address = Cells(row, 1)
Do Until Trim(address) = ""
Cells(row, 2) = PingAddress(address)
Cells(row, 3) = Now
row = row + 1
address = Cells(row, 1)
Sleep 2500
Loop
End Sub

Function PingAddress(strDomain As String) As String
Dim objShell As Object
Dim strText As String
Dim objExecObject As Object
Set objShell = CreateObject("WScript.Shell")
Dim objIP As clsIPResolve
Set objIP = New clsIPResolve
PingAddress = objIP.NameToAddress(strDomain)
If PingAddress = "" Then
  PingAddress = "Not found"
Else
  Set objExecObject = objShell.Exec("%comspec% /c ping -n 3 -w 1000 " & PingAddress)
  Do While Not objExecObject.StdOut.AtEndOfStream
  strText = objExecObject.StdOut.ReadAll()
  If InStr(strText, "unreachable") > 0 Then
    PingAddress = "Host unreachable"
  ElseIf InStr(strText, "Reply") > 0 Then
    Else
    PingAddress = "Not found"
  End If
  Loop
End If
Set objIP = Nothing
Set objExecObject = Nothing
Set objShell = Nothing
End Function

Function Address2Name(strDomain As String) As String
Dim objShell As Object
Dim strText As String
Dim objExecObject As Object
Set objShell = CreateObject("WScript.Shell")
Dim objIP As clsIPResolve
Set objIP = New clsIPResolve
Address2Name = objIP.AddressToName(strDomain)
If Address2Name = "" Then Address2Name = "No DNS available"
Set objIP = Nothing
Set objExecObject = Nothing
Set objShell = Nothing
End Function

Create a class module with the following text:

Option Explicit
Private mbInitialized As Boolean
Const WSADescription_Len = 256
Const WSASYS_Status_Len = 128
Const AF_INET = 4&

Private Type HOSTENT
hName As Long
hAliases As Long
hAddrType As Integer
hLength As Integer
hAddrList As Long
End Type

Private Type WSADATA
wversion As Integer
wHighVersion As Integer
szDescription(0 To WSADescription_Len) As Byte
szSystemStatus(0 To WSASYS_Status_Len) As Byte
iMaxSockets As Integer
iMaxUdpDg As Integer
lpszVendorInfo As Long
End Type

Private Declare Function WSAStartup _
Lib "wsock32" _
(ByVal VersionReq As Long, _
WSADataReturn As WSADATA) _
As Long

Private Declare Function WSACleanup _
Lib "wsock32" _
() _
As Long

Private Declare Function WSAGetLastError _
Lib "wsock32" _
() _
As Long

Private Declare Function gethostbyaddr _
Lib "wsock32" _
(addr As Long, _
addrLen As Long, _
addrType As Long) _
As Long

Private Declare Function gethostbyname _
Lib "wsock32" _
(ByVal hostname As String) _
As Long

Private Declare Sub RtlMoveMemory _
Lib "kernel32" _
(hpvDest As Any, _
ByVal hpvSource As Long, _
ByVal cbCopy As Long)

'checks if string is valid IP address
Private Function CheckIP(IPToCheck As String) As Boolean
Dim TempValues
Dim iLoop As Long
Dim TempByte As Byte
On Error GoTo CheckIPError
TempValues = Split(IPToCheck, ".")
If UBound(TempValues) < 3 Then
  Exit Function
End If
For iLoop = LBound(TempValues) To UBound(TempValues)
  TempByte = TempValues(iLoop)
Next iLoop
CheckIP = True
CheckIPError:
End Function

'converts IP address from string to sin_addr
Private Function MakeIP(strIP As String) As Long
Dim vTemp
Dim lngTemp As Long
Dim iLoop As Long
On Error GoTo MakeIPError
vTemp = Split(strIP, ".")
For iLoop = 0 To (UBound(vTemp) - 1)
  lngTemp = lngTemp + (vTemp(iLoop) * (256 ^ iLoop))
Next iLoop
If vTemp(UBound(vTemp)) < 128 Then   lngTemp = lngTemp + (vTemp(UBound(vTemp)) * (256 ^ 3)) Else   lngTemp = lngTemp + ((vTemp(UBound(vTemp)) - 256) * (256 ^ 3)) End If MakeIP = lngTemp MakeIPError: End Function 'resolves IP address to host name Private Function AddrToName(strAddr As String) As String Dim heEntry As HOSTENT Dim strHost As String * 255 Dim strTemp As String Dim lngRet As Long Dim lngIP As Long On Error GoTo AddrToNameError If CheckIP(strAddr) Then   lngIP = MakeIP(strAddr)   lngRet = gethostbyaddr(lngIP, 4, AF_INET)   If lngRet = 0 Then     Exit Function   End If   RtlMoveMemory heEntry, lngRet, Len(heEntry)   RtlMoveMemory ByVal strHost, heEntry.hName, 255   strTemp = TrimNull(strHost)   AddrToName = strTemp End If AddrToNameError: End Function 'resolves host name to IP address Private Function NameToAddr(ByVal strHost As String) Dim ip_list() As Byte Dim heEntry As HOSTENT Dim strIPAddr As String Dim lp_HostEnt As Long Dim lp_HostIP As Long Dim iLoop As Integer On Error GoTo NameToAddrError lp_HostEnt = gethostbyname(strHost) If lp_HostEnt = 0 Then   Exit Function End If RtlMoveMemory heEntry, lp_HostEnt, LenB(heEntry) RtlMoveMemory lp_HostIP, heEntry.hAddrList, 4 ReDim ip_list(1 To heEntry.hLength) RtlMoveMemory ip_list(1), lp_HostIP, heEntry.hLength For iLoop = 1 To heEntry.hLength   strIPAddr = strIPAddr & ip_list(iLoop) & "." Next strIPAddr = Mid(strIPAddr, 1, Len(strIPAddr) - 1) NameToAddr = strIPAddr NameToAddrError: End Function Public Function AddressToName(strIP As String) As String If mbInitialized Then   AddressToName = AddrToName(strIP) End If End Function Public Function NameToAddress(strName As String) As String If mbInitialized Then   NameToAddress = NameToAddr(strName) End If End Function Private Function TrimNull(sTrim As String) As String Dim iFind As Long iFind = InStr(1, sTrim, Chr(0)) If iFind > 0 Then
  TrimNull = Left(sTrim, iFind - 1)
Else
  TrimNull = sTrim
End If
End Function

Private Sub Class_Initialize()
Dim wsa As WSADATA
mbInitialized = (WSAStartup(257, wsa) = 0)
End Sub

Private Sub Class_Terminate()
If mbInitialized Then
  WSACleanup
End If
End Sub

You now have a GetIPs function