How to Use the VLOOKUP Function to Return Only One Value From Multiple Columns (2 Methods)

The VLOOKUP function is a powerful tool for extracting information from a range of data within the same or different worksheets in Excel. However, by default, it only allows you to search for one specific value at a time. In this guide, we’ll explore two methods to modify the VLOOKUP function so that it can return a single value from multiple columns.


Method 1 – Using the Standard VLOOKUP to Return Only One Value from Multiple Columns:

Imagine you’re working in a supermarket, and your worksheet contains information about products, including their “Item ID,” “Product Name,” and “Price.”

Sample Dataset to VLOOKUP from Multiple Columns with Only One Return

Read More: 10 Best Practices with VLOOKUP in Excel


1.1 Using Standard VLOOKUP within the Same Worksheet

Suppose you want to find the price of a specific product with a particular ID, such as the product “Knife” with ID “M-04.” Follow these steps:

  • Create a Lookup Column:
    • Add a new column called Lookup Column to your table array. This column should be the leftmost column because the VLOOKUP function searches from left to right.
    • Create a table anywhere in the worksheet where you want to get the price for the product Knife with ID M-04.

Lookup Column

    • In this column, use the CONCATENATE function to merge the values from the Item ID and Product columns. For example, if cell C5 contains the Item ID and cell D5 contains the Product, enter the formula in cell B5:
=CONCATENATE(C5,D5)
    • Press Enter to get the merged values.

Use the CONCATENATE Function to Extract Lookup Column Data

    • Use the Fill handle feature to copy the same formula down to B16.

Lookup Column Values to VLOOKUP from Multiple Columns with Only One Return

  • Apply VLOOKUP:
    • In cell H6 (or any other cell where you want the result), use the VLOOKUP function to find the price for the product Knife with ID M-04. The formula should look like this:
=VLOOKUP(H4&H5,B5:E16,4,FALSE)
  • Press Enter.

Standard VLOOKUP Formula to VLOOKUP from Multiple Columns with Only One Return

Formula Breakdown

  • H4 & H5 concatenates the values in the Lookup Column (created earlier) to search for the specific product.
  • B5:E16 represents the table array containing the data.
  • indicates the column index (in this case, the Price column).
  • [range_lookup] is set to FALSE for an exact match.

By following these steps, you’ll be able to use VLOOKUP to retrieve a single value from multiple columns within the same worksheet.

Read More: 7 Practical Examples of VLOOKUP Function in Excel


1.2 Using VLOOKUP across Different Worksheets

Suppose your data array is in a different worksheet (let’s call it M01), and you want to perform the same operation in another worksheet (M02). Here’s how:

  • Set up a table in the M02 worksheet where you want to find the price using the VLOOKUP function.

Created Table to Insert Formula

  • In cell D5 (or any other cell where you want the result), use the following formula to retrieve the price from the M01 worksheet:
=VLOOKUP(B5&C5,'M01'!B5:E16,4,FALSE)
  • Press Enter.

Formula to VLOOKUP from Multiple Columns with Only One Return in Different Worksheet

Formula Breakdown

  • B5 & C5 concatenates the values from the Item ID and Product columns.
  • ‘M01’!B5:E16 specifies the table array in the M01 worksheet.
  • represents the column index (the Price column).
  • [range_lookup] is set to FALSE for an exact match.

By following these steps, you’ll obtain the lookup value from multiple columns in a different worksheet using VLOOKUP.


Method 2 – Using Multiple VLOOKUP Functions to Return a Single Value from Multiple Columns:

2.1 Using Multiple VLOOKUP within the Same Worksheet

Suppose you have two tables in the same worksheet—one with Item ID and Product columns, and the other with Product and Price columns. Your goal is to find the price using a nested VLOOKUP formula. Follow these steps:

Sample Dataset to Apply Nested VLOOKUP Formula to VLOOKUP from Multiple Columns with Only One Return

Follow the steps below to accomplish this target.

  • Set up a table anywhere in the worksheet where you want to retrieve the value from multiple columns.

Created Table to Insert Formula to VLOOKUP from Multiple Columns with Only One Return in Excel

  • In cell I5 (or any other cell where you want the result), use the following formula:
=VLOOKUP(VLOOKUP(H5,B$5:C$16,2,FALSE),E$5:F$16,2,FALSE)
  • Press Enter. This approach allows you to return only one value from multiple columns.

Nested VLOOKUP Function to VLOOKUP from Multiple Columns with Only One Return in Excel

Formula Breakdown

  • The inner VLOOKUP (VLOOKUP(H5, B$5:C$16, 2, FALSE))pulls the Product based on the lookup value.
  • The outer VLOOKUP then searches for the price using the product as the lookup value.
  • B$5:C$16 represents the first table array (Item ID and Product).
  • E$5:F$16 represents the second table array (Product and Price).
  • Use the Fill handle tool to copy down the same formula dynamically.

Nested VLOOKUP Function to Get All the Lookup Values from Multiple Columns with Only One Return

This approach allows you to return only one value from multiple columns.


2.2 Using Multiple VLOOKUP across Different Worksheets

Now let’s consider a scenario where the data tables are in different worksheets (W1 and W2). Follow these steps:

  • Create Data Tables:
    • In the W1 worksheet, set up a table called Data Range 1 containing relevant data.

Data Range 1

    • In the W2 worksheet, create another table called Data Range 2.

Data Range 2

  • Create a Result Table:
    • In a new worksheet, create a table where you want to display the results.

Table to Apply Nested VLOOKUP Function to VLOOKUP from Multiple Columns with Only One Return

  • Apply Nested VLOOKUP:
    • In cell C5 (or any other cell where you want the result), use the following formula:
=VLOOKUP(VLOOKUP(B5,'W1'!B$5:C$16,2,FALSE),'W2'!B$5:C$16,2,FALSE)
  • Press Enter to return only Price from multiple columns lookup.

Nested VLOOKUP Function to Lookup Value from Multiple Columns with Only One Return in Different Worksheet

Formula Breakdown

  • The inner VLOOKUP (VLOOKUP(B5, ‘W1’!B$5:C$16, 2, FALSE)) retrieves the product from the W1 sheet.
  • The outer VLOOKUP then searches for the price using the product as the lookup value in the W2 sheet.
  • ‘W1’!B$5:C$16 represents the first table array.
  • ‘W2’!B$5:C$16 represents the second table array.
  • Use the Fill Handle tool to apply the same formula for the rest of the Item ID.

All LOOKUP Values with Nested VLOOOKUP Function

 


How to Apply Excel VLOOKUP with Multiple Column Index Numbers?

Suppose you need to look up multiple values simultaneously using a single VLOOKUP function. You can achieve this by using multiple-column index numbers. For example, if you have Item ID, Product, and Price in your dataset, and you want to return both the product and price for the item with ID M-09, follow these steps:

Sample Dataset to Apply Multiple Column Index Numbers in VLOOKUP

 

  • Set up a table where you want to display the results.

Create Table to Apply Formula

  • Select cells G5:H5 (or any other range where you want the results).

Select Cells to Apply VLOOKUP Formula with Multiple Column Index Numbers

  • Insert the following formula and press Ctrl+Shift+Enter (or just Enter for Excel 365 users):
=VLOOKUP(F5,B5:D16,{2,3},FALSE)
    • This formula returns multiple lookup values with multiple-column index numbers.
    • {2, 3} specifies the column indices for Product and Price.

VLOOKUP Formula with Multiple Column Index Numbers

Applied VLOOKUP with Multiple Column Index Numbers


Things to Remember

  • The VLOOKUP function always searches from left to right.
  • Avoid entering a column index less than 1 (which would result in an error).
  • Use absolute cell references ($) to lock the table array.
  • Always set the fourth argument to “FALSE” for an exact match.

Download Practice Workbook

You can download the practice workbook from here:


Related Articles


<< Go Back to Advanced VLOOKUPExcel VLOOKUP Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Asikul Himel
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo