## 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 row As Integer
row = 2
Cells(row, 3) = Now
row = 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
Else
Set objExecObject = objShell.Exec("%comspec% /c ping -n 3 -w 1000 " & PingAddress)
Do While Not objExecObject.StdOut.AtEndOfStream
If InStr(strText, "unreachable") > 0 Then
ElseIf InStr(strText, "Reply") > 0 Then
Else
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
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 WSASYS_Status_Len = 128
Const AF_INET = 4&

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

wversion As Integer
wHighVersion As Integer
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, _
As Long

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

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

Lib "wsock32" _
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

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
TrimNull = Left(sTrim, iFind - 1)
Else
TrimNull = sTrim
End If
End Function

Private Sub Class_Initialize()