how to use vlookup in excel with example

#How to use vlookup in excel with example how to#

The VLOOKUP function can perform a dynamic two-way lookup with the help of the MATCH function of Excel.The VLOOKUP function of Excel can retrieve a value based on the supplied lookup value, table array, and column index number.We guarantee a connection within 30 seconds and a customized solution within 20 minutes. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. The final result in cell H4 is 100, which is the value for Orders for Product ID 1001 with color Blue. Orders is in the fourth column of the table_array so col_index_num is 4. Enter the formula: = VLOOKUP(H2&H3,B2:E7,4,FALSE)įor the lookup_value, we link H2 and H3 ( H2&H3 ) using the “&” AND logical operator. To use VLOOKUP with multiple criteria, we follow these steps: Entering the formula for VLOOKUP with multiple criteria We want to retrieve the value for orders for Product ID “1001” and Color “Blue”.įigure 4. Modified source data in using VLOOKUP with multiple criteria Using VLOOKUP with Multiple Criteria

We insert a column “Lookup Value” to the left of the “Product ID” column, where we link the values of columns “Product ID” and “Color”.įigure 3. In order for us to lookup into two columns, we need to add a helper column in the lookup table.

VLOOKUP in Excel is limited to handle only one lookup value, which is always in the first column of the lookup table. In cell G4, we want to retrieve the value for orders for Product ID “1001” and Color “Blue”. Sample data in using VLOOKUP with multiple criteria

if FALSE, VLOOKUP will only find an exact matchīelow table has four columns: “Product ID” (column B), “Color” (column C) and “Orders” (column D).įigure 2.

if TRUE or omitted, VLOOKUP returns either an exact or approximate match.

: optional value is either TRUE or FALSE.

col_index_num : the column number in the table_array corresponding to the information we want to retrieve, relative to the lookup_value.

table_array : the range of cells in the source table containing the data we want to retrieve.

lookup_value1 and lookup_value2 : modified lookup value for VLOOKUP with multiple criteria lookup_value1 and lookup_value2 are the values we want to search and find in the table_array the ampersand “ & ” works as an AND logical operator.

The parameters of the VLOOKUP function are:

= VLOOKUP ( lookup_value1&lookup_value2, table_array, col_index_num, ) To look up with multiple criteria, the syntax is: Syntax VLOOKUP is used when we want to look up and retrieve data from a given data set. Final result: Use VLOOKUP with multiple criteriaįinal formula: =VLOOKUP(H2&H3,B2:E7,4,FALSE) Syntax of the VLOOKUP function This step by step tutorial will assist all levels of Excel users in using VLOOKUP with multiple criteria.įigure 1. This can be done by modifying the lookup value in the VLOOKUP formula and modifying the source table accordingly. While working with Excel, there are instances when we need to lookup a value based on multiple criteria.

#How to use vlookup in excel with example how to#

How to Use VLOOKUP with Multiple Criteria