What is the shortcut key for VLOOKUP?

Hi, I copied a macro from Bill Manville, and added a couple of blocks where necessary ... this changed the action of the macro to halt after it has navigated to the first multicell range in the precedents of the active cell. In a normal module ( in the VBE do Insert Module ) code window paste this:

Code:

Sub GoToPrecedents()

    ' adapted from Sub FindPrecedents()
    ' written by Bill Manville
    ' With edits from PaulS
    ' adapted by Glenn Bamford - goto the first multicell precedent
    ' this procedure finds the cells which are the direct precedents of the active cell
    Dim rLast As Range, iLinkNum As Integer, iArrowNum As Integer
    Dim stMsg As String
    Dim bNewArrow As Boolean
    Application.ScreenUpdating = False
    ActiveCell.ShowPrecedents
    Set rLast = ActiveCell
    iArrowNum = 1
    iLinkNum = 1
    bNewArrow = True
    Do
        Do
            Application.Goto rLast
            On Error Resume Next
            ActiveCell.NavigateArrow TowardPrecedent:=True, ArrowNumber:=iArrowNum, LinkNumber:=iLinkNum
            If Err.Number > 0 Then Exit Do
            On Error GoTo 0
            If rLast.Address(external:=True) = ActiveCell.Address(external:=True) Then Exit Do
            bNewArrow = False
            If rLast.Worksheet.Parent.Name = ActiveCell.Worksheet.Parent.Name Then
                If rLast.Worksheet.Name = ActiveCell.Parent.Name Then
                    ' local
                    stMsg = stMsg & vbNewLine & Selection.Address
                Else
                    If Selection.Cells.Count > 1 Then ' Glenn Bamford
                        rLast.Parent.ClearArrows ' Glenn Bamford
                        Exit Sub ' drop out early, to match requirements - Glenn Bamford
                    End If  ' Glenn Bamford
                    stMsg = stMsg & vbNewLine & "'" & Selection.Parent.Name & "'!" & Selection.Address
                End If
            Else
                ' external
                    If Selection.Cells.Count > 1 Then ' Glenn Bamford
                        rLast.Parent.ClearArrows ' Glenn Bamford
                        Exit Sub ' drop out early, to match requirements - Glenn Bamford
                    End If  ' Glenn Bamford
                stMsg = stMsg & vbNewLine & Selection.Address(external:=True)
            End If
            iLinkNum = iLinkNum + 1  ' try another link
        Loop
        If bNewArrow Then Exit Do
        iLinkNum = 1
        bNewArrow = True
        iArrowNum = iArrowNum + 1  'try another arrow
    Loop
    rLast.Parent.ClearArrows
    Application.Goto rLast
    If stMsg <> "" Then MsgBox "Precedents are" & stMsg
    Exit Sub

End Sub

The VLOOKUP Function Vlookup stands for vertical lookup. It searches for a value in the leftmost column of a table. Then returns a value a specified number of columns to the right from the found value. It is the same as a hlookup, except it looks up values vertically instead of horizontally.

(Notice how the formula inputs appear)

VLOOKUP Function Syntax and Arguments:

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

lookup_value – The value you want to search for.

table_array – The data range that contains both the value you want to search for and the value you want the vlookup to return. The column containing the search values must be the left-most column.

col_index_num – The column number of the data range, from which you want to return a value from.

range_lookup – TRUE or FALSE. FALSE searches for an exact match. TRUE searches for the nearest match that is less than or equal to the lookup_value. If TRUE is chosen the left-most column (the lookup column) must be sorted ascendingly (lowest to highest).

What is the VLOOKUP function?

As one of the older functions in the world of spreadsheets, the VLOOKUP function is used to do Vertical Lookups. It has a few limitations that are often overcome with other functions, such as INDEX/MATCH. However, it does have the benefit of being a single function that can do an exact match lookup by itself. It also tends to be one of the first functions people learn about.

Basic example

Let’s look at a sample of data from a grade book. We’ll tackle several examples for extracting information for specific students.

If we want to find what class Bob is in, we would write the formula:

=VLOOKUP("Bob", A2:C5, 2, FALSE)

Side tip: You can also use the number 0 instead of False as the final argument, as they have the same value. Some people prefer this as it’s quicker to write. Just know that both are acceptable.

Shifted Data

To add some clarification to our first example, the lookup item doesn’t have to be in column A of your spreadsheet, just the first column of your search range. Let’s use the same data set:

Now, let’s find the grade for the class of Science. Our formula would be

=VLOOKUP("Science", B2:C5, 2, FALSE)

Wildcard usage

The VLOOKUP function supports the use of the wildcards “*” an “?” when doing searches. For instance, let’s say that we’d forgotten how to spell Frank’s name, and just wanted to search for a name that starts with “F”. We could write the formula

=VLOOKUP("F*", A2:C5, 2, FALSE)

This would be able to find the name Frank in row 5, and then return the value from 2nd relative column.  In this case, the answer will be “Science”.

Non-exact match

Most of the time, you’ll want to make sure that the last argument in VLOOKUP is False (or 0) so that you get an exact match. However, there are a few times when you might be searching for a non-exact match. If you have a list of sorted data, you can also use VLOOKUP to return the result for the item that is either the same, or next smallest. This is often used when dealing with increasing ranges of numbers, such as in a tax table or commission bonuses.

Let’s say that you want to find the tax rate for an income entered cell D2. The formula in D4 can be:

=VLOOKUP(D2, A2:B6, 2, TRUE)

The difference in this formula is that our last argument is “True”. In our specific example, we can see that when our individual inputs an income of $45,000 they will have a tax rate of 15%.

Note: Although we usually are wanting an exact match with False as the argument, it you forget to specify the 4th argument in a VLOOKUP, the default is True. This can cause you to get some unexpected results, especially when dealing with text values.

Dynamic column

VLOOKUP requires you to give an argument saying which column you want to return a value from, but the occasion may arise when you don’t know where the column will be, or you want to allow your user to change which column to return from. In these cases, it can be helpful to use the MATCH function to determine the column number.

Let’s consider our grade book example again, with some inputs in E2 and E4. To get the column number, we could write a formula of

=MATCH(E2, A1:C1, 0)

This will try to find the exact position of “Grade” within the range A1:C1. The answer will be 3. Knowing this, we can plug it into a VLOOKUP function and write a formula in E6 like so:

=VLOOKUP(E4, A2:C5, MATCH(E2, A1:C1, 0), 0)

So, the MATCH function will evaluate to 3, and that tells the VLOOKUP to return a result from the 3rd column in the A2:C5 range. Overall, we then get our desired result of “C”. Our formula is dynamic now in that we can change either the column to look at or the name to search for.

VLOOKUP limitations

As mentioned at the beginning of the article, the biggest downfall of VLOOKUP is that it requires the search term to be found in the left most column of the search range. While there are some fancy tricks you can do to overcome this <link to CHOOSE article>, the common alternative is to use INDEX and MATCH. That combo gives you more flexibility, and it can sometimes even be a faster calculation.

VLOOKUP in Google Sheets

The VLOOKUP Function works exactly the same in Google Sheets as in Excel:

Additional Notes

Use the VLOOKUP Function to perform a VERTICAL lookup. The VLOOKUP searches for an exact match (range_lookup = FALSE) or the closest match that is equal to or less than the lookup_value (range_lookup = TRUE, numeric values only) in the first row of the table_array. It then returns a corresponding value, n number of rows below the match.

When using an VLOOKUP to find an exact match, first you define an identifying value that you want to search for as the lookup_value. This identifying value might be a SSN, employee ID, name, or some other unique identifier.

Next you define the range (called the table_array) that contains the identifiers in the top row and whatever values that you ultimately wish to search for in the rows below it. IMPORTANT: The unique identifiers must be in the top row. If they are not, you must either move the row to the top, or use MATCH / INDEX instead of the VLOOKUP.

Third, define row number (row_index) of the table_array that you wish to return. Keep in mind that the first row, containing the unique identifiers is row 1. The second row is row 2, etc.

Last, you must indicate whether to search for an exact match (FALSE) or nearest match (TRUE) in the range_lookup. If the exact match option is selected, and an exact match is not found, an error is returned (#N/A). To have the formula return blank or “not found”, or any other value instead of the error value (#N/A) use the IFERROR Function with the VLOOKUP.

To use the VLOOKUP Function to return an approximate match set: range_lookup = TRUE. This option is only available for numeric values. The values must be sorted in ascending order.

Is VLOOKUP the same as Ctrl F?

Why F4 is used in VLOOKUP?

What is Ctrl F2 in Excel?

Where is the VLOOKUP button?