How to Use a Custom VLOOKUP Formula in Excel Data Validation – 2 Methods

The sample dataset showcases salespersons’ sales in different regions.

Method 1 – Using a Drop-down List of Data Validation with the VLOOKUP Function in Excel

Use the Drop-Down list feature of Data Validation and the VLOOKUP function.

Steps:

• Select D11.
• Click: Data > Data Tools > Data Validation > Data Validation.

• Select Settings.
• InAllow, choose List.
• In Source, click Open.

• Select the salespersons’ names.
• Press Enter.

• Click OK.

• A drop-down icon will be displayed beside the selected cell.
• Click it to see the list.
• Select Sam and use the VLOOKUP function to find his sales and region.

• To find the region, enter the following formula in D12:

• Press Enter to see the output.

To find the sales in D13:

• Enter the following formula.

• Press Enter to see the output.

Follow the same procedure for the other salespersons.

Read More: How to Use IF Statement in Data Validation Formula in Excel

Method 2 – Applying Dynamic Data Validation with a Multiple VLOOKUP Formula

Validate data for given criteria using the Data Validation tool and a double VLOOKUP function. The function returns TRUE if criteria are met. Otherwise, FALSE.

The dataset showcases gadgets’ prices.

Steps:

• In D11 enter the following formula:

• Press Enter to see the output.

• Drag down the Fill Handle to see the other results.

This is the final output:

Formula Breakdown:

C11<=VLOOKUP(B11,B5:D8,3,0)
finds the upper range for the value of B11 and checks whether the value of C11 is less than or equal to the output of the VLOOKUP function. It returns TRUE

C11>=VLOOKUP(B11,B5:D8,2,0)
finds the lower range for the value of B11 checks whether the value of C11 is greater than or equal to the output of the VLOOKUP function. It returns TRUE

AND(C11>=VLOOKUP(B11,B5:D8,2,0),C11<=VLOOKUP(B11,B5:D8,3,0))
The AND function combines both outputs. If both return TRUE, it returns TRUE. If one returns FALSE, it returns FALSE.

Read More:  Apply Custom Data Validation for Multiple Criteria in Excel

Download Practice Workbook

Download the free Excel template here and practice.

Related Articles

• How to Apply Multiple Data Validation in One Cell in Excel
• How to Remove Blanks from Data Validation List in Excel
• Data Validation Based on Another Cell in Excel
• How to Copy Data Validation in Excel
• How to Remove Data Validation Restrictions in Excel

<< Go Back to Excel Custom Data Validation | Data Validation in Excel | Learn Excel