VLOOKUP for dummies

VLOOKUP for dummies

The simplest way to retrieve data in a database using a one criteria is use of VLOOKUP function. It is good to know that VLOOKUP function can be used with two or more criteria columns.

The method is as simple as with one criteria and procedure is explained with a simple example.

Also note that there are some other functions in Excel that may be used as an alternative to VLOOKUP such as LOOKUP, MATCH, INDEX, etc.

1 - Let’s consider that we have a data table of a car sale and we want to look up some data with multiple criteria.

VLOOKUP for dummies

2 - We want to retrieve the price of a car using car make and model. Note that the car make and model are in columns. For this we can make a simple table to retrieve the required information.

VLOOKUP for dummies

3 - We are using the traditional lookup function (VLOOKUP) along with the CONCATENATE function. So we have to first create a single lookup column first.

This can be easily done with following CONCATENATE Syntax, as shown below. Which combines cells C13 and D13.

=CONCATENATE(C13,D13)

The new lookup column is illustrated in the first column below:

VLOOKUP for dummies

The Lookup column joins values from C and D columns that are used as criteria. It must be the first column of the data table, and functions as the key column for LOOKUP function.

4 - Now we have a single lookup column (or a helper column) that can be used with a traditional lookup function to retrieve the required information.

VLOOKUP for dummies

lookup_value:

Lookup value is set as B7 & C7, which are the make and model of car.

table_array:

Set up a VLOOKUP formula that refers to a table that includes the helper column. The helper column must be the first column in the table.

col_index_num:

The column number of the retrieving data is no.5 (i.e. Price $).

range_lookup:

Since we want to retrieve the exact match, False is given as the range_lookup

5 - Now the function is set to retrieve data from the table.

If the data retrieving cannot be accomplish for the given Make and Model, the result will be given as N/A.

VLOOKUP for dummies

 

Related Trainings

Badly Formatted Data

Badly Formatted Data

Cleaning up data using various text functions which were given below. You will also correct some datas in the table.


Beverage Sales

Beverage Sales

Drill down your sales data using an alternative lookup method.


Practice: Separation and merge

Practice: Separation and merge

Practice your text functions skills.