VLOOKUP in Excel and Google Sheets

---

Try using mapflo for data analysis. It’s great for joining datasets. Get Started for Free.

---

Summary

VLOOKUP function looks up a value in a column and returns the value in a corresponding column.

VLOOKUP is one of several formulas that allow you to join data sets together.

The formula is: =VLOOKUP(search_key, range, index, [is_sorted])

Where:

• search_key => “find this value”
• range => ”in this range of cells”
• Index => ”once found output the value this number of columns to the right”
• [is_sorted] => ”use 0 for exact match or 1 for close match”

 

CONTENTS

• Summary
• Introduction
• VLOOKUP example based on one value
• VLOOKUP example based on two values

INTRODUCTION

VLOOKUP is short for vertical Lookup – the vertical part means looking across columns rather than down horizontal rows (where you can use HLOOKUP).

It works the same in Google Sheets and Excel

VLOOKUP finds a value in a data range and outputs the value that is the requested number of columns to the right of the found value.

The formula is: =VLOOKUP(search_key, range, index, [is_sorted])

• search_key => “find this value”
• range => ”in this range of cells”
• Index => ”once found output the value this number of columns to the right”
• [is_sorted] => ”use 0 for exact match or 1 for close match”

Exact match means find exactly the value in the search_key. Close match means find the closest value in the range to the value in the search_key.

The search key can:

• be a string (it needs inverted commas around it) e.g. =vlookup(“Tops”,A2:B5,2,0)
• be a number, e.g. =vlookup(5,A2:B5,2,0)
• be a reference, e.g. =vlookup(E5,A2:B5,2,0)
• use the wildcards question mark (?) and asterisk (*). Question mark standing in for a single character and asterisk standing in for any series of characters. To match an actual question mark or asterisk then prefix a tilda (~) before the the character and an extra tilda if you are trying to match a search_key with an actual tilde in it.

VLOOKUP EXAMPLE BASED ON ONE VALUE

Below is a simple data table showing Items Sold by Product Category:

If we want to find out how many Tops were sold then we can use the following formula:

=vlookup(“Tops”,A2:B5,2,0)

• search_key => find “Tops”
• range => in the range A2:B5
• Index => then count 2 columns to the right (where the matched value is  column 1)
• [is_sorted] => 0 means an exact match

The formula outputs 40 [the number of Tops sold]

VLOOKUP EXAMPLE BASED ON TWO VALUE

We have Items Sold by Month and Product Category and in a separate table Revenue by Month and Product Category. How do we use VLOOKUP to combine the data into one table?

We can create a unique value by concatenating (joining together) Product Category and Month.

The concatenate formula is simple, you use an & between cell references. See below:

Now there is a unique value for each row of each table that looks like ‘SandalsJan-22’

We can use this unique row to do the VLOOKUP:

Using VLOOKUPS to join datasets

You can use VLOOKUPs combined with concatenate to join datasets. You need to use concatenate to join together dimensions that together create unique values (like Month and Product Category in the example above). You can then vlookup the concatenated value in the second dataset to append values from that dataset to the initial data table.

Mapflo makes joining data sets really 

mapflo makes joining datasets really easy.

instead of using concatenate and VLOOKUP you can select one or more columns that you want the values to match to create the join. All other columns will be automatically joined to the original dataset (and you can use Data Shaper Node to re-order or remove columns)