Excel tutorials

The only Excel books you will ever need

  • The only constantly updated Excel 365 titles more…
  • Thoroughly covers Power Pivot and Power Query more…
  • Covers business intelligence and OLAP features more…
  • Simple, easy, understandable, 2-page lessons more…
  • Available as both printed books and e-Books more…
  • Used by schools, colleges and universities  more…
  • Available for 365, 2021, 2019, 2016 and Mac versions more…

VLOOKUP using text strings

What is VLOOKUP?

VLOOKUP is one of the functions in Excel’s vast function library. Its purpose is to search a table of values for a corresponding item and return a matching item from the table.  

For Excel 365 users there is now a modern replacement for VLOOKUP called XLOOKUP.  XLOOKUP has many more features and should be used for all new work (unless compatibility with legacy versions of Excel is required). The new XLOOKP function (along with all of the other modern array functions) is covered in depth in the Excel 365 Expert Skills book.

An example of the legacy VLOOKUP function might be to search a table of price data for a certain product name and returning the matching product’s price.

If you’re unfamiliar with Excel functions, you might want to work through our completely free Basic Skills course, which explains the basics of Excel formulas and functions.

What is VLOOKUP useful for?

VLOOKUP is generally used when you have two tables of data and need to extract values from one into the other, as shown above.

In the example above you want to extract the price for each product into column E. To do this you would use VLOOKUP to search for the product name in the table of prices and return the Unit Price for the matching product.

Can VLOOKUP work with text as well as numbers?

Yes. VLOOKUP can search for textual values just as well as it can search for numbers. The example above would search for the product names, which are text rather than numbers.

Remember, of course, that the values being searched should be unique. VLOOKUP stops searching when it finds a single matching item, so it won’t find any further matches if they exist. If there were two entries for a product in the Prices table, VLOOKUP would only return the first value.

How to use VLOOKUP

Let’s look at the example again:

In cell D1 you need to search for the price of Thuringer Rostbratwurst in the Prices table (cells I4:J88) and return the matching price. The VLOOKUP formula to do this is:


The formula’s arguments (the values in brackets) work like this:

  1. Cell C4 contains the value you are searching for.
  2. Cells I4:J88 contain the data that needs to be searched. VLOOKUP always looks in the first column of this data (column I) for the lookup value.
  3. You want VLOOKUP to return the value from column 2 of the table (the prices).
  4. You only want VLOOKUP to return a value if it finds an exact match, so the last argument is FALSE.

VLOOKUP is explained in much more depth in our Expert Skills course, which also explains how to use inexact VLOOKUP functions to return the nearest match.

After filling down the VLOOKUP formula you can multiply the quantity by the price to extract the total.

You can download a copy of the workbook showing the formulas in action.

What isn't VLOOKUP useful for?

It’s important to remember that Excel was designed to be a spreadsheet tool rather than a database. VLOOKUP allows you to make Excel behave in a similar way to database software, but without many of the features of a ‘real’ database.

If you need to create a complex database you should use software that is made for that purpose such as SQL Server, Access or MySQL. You can then use Excel to connect to that database and analyze the data.

That said, Excel has incorporated many new features to make it easier to work with relational data. The new Power Pivot and Get & Transform tools allow you to create relationships between Excel tables and query databases in new and vastly  better ways. Our Expert Skills course explains how to use these, as well as explaining relationships and databases in much more depth.

These are the only up-to-date Excel books currently published and includes the new Dynamic Arrays features.

They are also the only books that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.

Share this article

Recent Articles

VLOOKUP inexact match illustration (chicken and egg).

VLOOKUP inexact match

VLOOKUP lesson with sample file that will teach you everything there is to know when creating a VLOOKUP inexact match Excel function.

VLOOKUP exact match

Simple VLOOKUP tutorial with sample file that will teach you everything there is to know when creating a VLOOKUP function with an exact match.


Excel XLOOKUP function (part one)

The new Excel XLOOKUP function was introduced in the July 2020 Excel 365 semi-annual update.  It isn’t available in older versions (Excel 2019 and earlier). 

2 Responses

    1. Hi Solomon

      I understand from your comment that you need to concatenate two string values together.

      You can easily do this using the concatenation operator (&) or the CONCAT function. Both methods are covered in depth in Session 3 of the Expert Skills book.

      In the example above this formula:

      =C4 & ” Price: $” & VLOOKUP(C4,I4:J88,2,FALSE)

      … would return:

      Thüringer Rostbratwurst Price: $99

Leave a Reply

Your email address will not be published. Required fields are marked *

Which tutorial do you need?