davesexcel.com

Excel information

VLOOKUP WEEK - MARCH 25-31, 2012

The Vlookup function has been widely misunderstood.

There are thousands of vlookup questions on the internet.

 

 Create a simple table.

 

 Create a drop-down menu so you can easily select the name 

Select G3 and then select the data ribbon and Data Validation

In the Allow box select "List", then select the Source box. When you are in the Source Box highlight the range A2:A7, this is what will show in the drop-down menu.Then hit OK.

 

 Creating the Vlookup Formula

The table consists of three columns A,B,C in this example

 

To get the street number for Sally Ann, we would want to find Sally Ann in the 1st column of the table and return a the result from the second column of the table, the street name would be the third column of the table.

Select F3 and start typing =Vlookup( if you have xl'07 and newer, excel will show the functions as you type

 

Click the fx symbol in the formula bar to open the formula window .

 Fill in the boxes, in Lookup_Value select E3

In Table_Array select A3:C7

Col_Index_Num is 2 because you want the result from the 2nd column in the table.

Range_Lookup is false, this means you want the exact match.

 

 To find the Street Name Change the 2 to a 3 in the G3 formula., because you want the result from the 3rd column.

 

There has been confusion with the Vlookup function and it usually has to do with the Column_Index part.

We use column 2 as the index column not because Column B is Column 2, 

If we had the table in range M2:O7 the Column_Index would still be 2 to find the Street Number.

Select a name from the dropdown menu to get the Street number and Name.

 

 Vlookup example.xls

Here are other Vlookup examples found in the website.

 

Check it out

 

VLOOKUP Example2.xls

UserFormVlookup ForTextBoxes.xls

 Vlookup Other sheets.xls

 

 

 

 

Vlookup in a UserForm

We can use Vlookup in VBA code.

The code for Vlookup is

 something = WorksheetFunction.VLookup(Arg1, Arg2, Arg3, Arg4)

The function is relativily the same, except we are using Combobox1 as the item to lookup.

Build your UserForm

Click here if you have never built a UserForm.

In the UserForm module use this code to populate the ComboBox when the UserForm Shows.

 Private Sub UserForm_Initialize()

    Dim Rws As Long, Rng As Range

    Rws = Cells(Rows.Count, "A").End(xlUp).Row

    Set Rng = Range(Cells(2, 1), Cells(Rws, 1))

    ComboBox1.List = Rng.Value

End Sub

When you make a selection in the ComboBox The Labels will show the result of the Vlookup function as well as 2 cells on the sheet.

Private Sub ComboBox1_Change()

    Dim Rws As Long, TRng As Range, x As Variant, y As Variant

    Rws = Cells(Rows.Count, "A").End(xlUp).Row

    Set TRng = Range(Cells(2, 1), Cells(Rws, 3))

    x = WorksheetFunction.VLookup(ComboBox1, TRng, 2, 0)
    y = WorksheetFunction.VLookup(ComboBox1, TRng, 3, 0)

    Label1 = x
    Label2 = y
    Range("E3") = ComboBox1
    Range("F3") = x
    Range("G3") = y

End Sub

Check out the example

Userform Vlookup.xlsm