<-- Return to Excel Statistics Guide

Excel Statistics Functions Guide

A Tool for Students

Keith Greiner Ed.D.

Updated August 9, 2020

The =VLOOKUP(…) Function

The VLOOKUP(…) function is used to look up a value in one column in a spreadsheet and return a corresponding value in the same row, but another column.

Below are templates and parameter lists for both the =VLOOKUP(...) function and my improvement on the function, called =VLOOKUP_kg(...).

= VLOOKUP(Lookup_Value, Table_Array, Col_Index, [Range_Lookup])

= VLOOKUP_kg(Value_to_Find, Range_to_Search_and_Return, Column_Select)

I do not generally use the =VLOOKUP(...) function unless the values to be searched are numerical. For alpha values, the function has not returned a value that is correct. At least that is my experience.

Excel Image 1 shows a set up to search a list of AM radio stations in the U. S. The list includes the station call letters, sorted in alpha order, along with the city and the state where the station is located. This is only a small portion of the total list, but the same experience will be found it the complete list is used. Notice that the values marked in green in row 10 are not the same as the searched value in cell H5, but the values marked in green in row 15 are the same as the searched value. The Values returned by =VLOOKUP(...) are incorrect, but the values returned by =VLOOKUP_kg are correct. That’s why I don’t use =VLOOKUP(…). Instead, I use either my own VBA function, =VLOOKUP_kg(…), or MS Access.

Excel Image 2. shows how the functions work when the column to be searched, column C, is numerical. For the purpose of demonstration I replaced the call letters with numerical values that are sorted in order from smallest to larges. Here, you can see that the results in row 10 are the same as the results in row 15. That means the two functions are returning the same value.

Below is the VBA code for the =VLOOKUP_kg(…) function and a test function called Run_VLOOKUP_kg(). The test function is used to test the code without having to use the Excel sheet.

<-- Return to Excel Statistics Guide