How to Use Custom VLOOKUP Formula in Excel Data Validation

Data Validation in Excel gives you the facilities to set a validation range of data that can help you in many ways like which data you can select or not or you can make a dropdown list that saves time. While using the VLOOKUP function we need to set a lookup value and for that data validation can make it easier. This article will provide you with 2 useful ways to use the custom VLOOKUP formula in Excel data validation.

You can download the free Excel template from here and practice on your own.

2 Ways to Use Custom VLOOKUP Formula in Excel Data Validation

Let’s get introduced to our dataset first, It represents some salespersons’ sales in different regions.

1. Use Drop-down List of Data Validation with VLOOKUP Function in Excel

In this method, we’ll use the Drop-Down list feature of Data Validation for the VLOOKUP function so that we can find the data for a lookup value easily. First, we’ll see how to make a drop-down list for Cell D11 and then we’ll use it for the VLOOKUP function.

Steps:

• Select Cell D11.
• Then click as follows: Data > Data Tools > Data Validation > Data Validation.

And soon after a dialog box will open up.

• From the Settings part, select List from the Allow drop-down box.
• Later, click on the Open icon from the Source box.

It will take you to set a range of data.

• Select the salespersons’ names by dragging with your mouse.
• Then press the Enter button and it will take you to the previous dialog box.

• The range is selected successfully, just press OK.

Then you will get a drop-down icon right beside the selected cell. By clicking it you will get the list. Let’s select Sam and use the VLOOKUP function now. We’ll use the VLOOKUP function using this list to find the sales and region separately but at a time.

• To get the region name, write the following formula in Cell D12–

• Then press the Enter button to get the output.

Now we’ll find the sales in Cell D13.

• Type the following formula in it-

• Later, press the Enter button to get the output.

Now if you want to get the region and sales for another salesperson just select the name from the drop-down list and you will get the corresponding output at a time.

Read More: How to Use Named Range for Data Validation List with VBA in Excel

Similar Readings:

• [Fixed] Data Validation Not Working for Copy Paste in Excel (with Solution)
• How to Remove Blanks from Data Validation List in Excel (5 Methods)
• Default Value in Data Validation List with Excel VBA (Macro and UserForm)
• How to Make a Data Validation List from Table in Excel (3 Methods)
• Excel Data Validation Drop Down List with Filter (2 Methods)

2. Apply Dynamic Data Validation with Multiple VLOOKUP Formula

Here, we’ll check the validity of data for given criteria using the Data Validation tool and double VLOOKUP function. If it meets the criteria then Excel will show TRUE otherwise FALSE. For that, I have used a new dataset here which represents some gadgets’ prices. I have selected a lower and upper price range for each item. Now I’ll check the given price whether it meets the criteria or not using the double VLOOKUP function.

Steps:

• In Cell D11 write the following formula-

• Later, press the Enter button for the result and it says that it is TRUE.

• Finally, just drag down the Fill Handle icon to get the other results.

Here’s all output-

💭 Formula Breakdown:

➥ C11<=VLOOKUP(B11,B5:D8,3,0)
Here the VLOOKUP function will find the upper range for the value of Cell B11 and then Excel will check the value of Cell C11 whether it is less than or equal to the output of the VLOOKUP function. So it will return as-
TRUE

➥ C11>=VLOOKUP(B11,B5:D8,2,0)
This VLOOKUP function will find the lower range for the value of Cell B11 and then Excel will check the value of Cell C11 whether it is greater than or equal to the output of the VLOOKUP function. So it returns-
TRUE

➥ AND(C11>=VLOOKUP(B11,B5:D8,2,0),C11<=VLOOKUP(B11,B5:D8,3,0))
Finally, the AND function will combine both outputs. If both outputs return TRUE then it will return TRUE. If any output returns FALSE then it will return FALSE. So finally the output will return as-
TRUE

Read More: How to Apply Multiple Data Validation in One Cell in Excel (3 Examples)

Conclusion

I hope the procedures described above will be good enough to use excel data validation with a custom VLOOKUP formula. Feel free to ask any question in the comment section and please give me feedback.

Related Articles

• Apply Custom Data Validation for Multiple Criteria in Excel (4 Examples)
• How to Use IF Statement in Data Validation Formula in Excel (6 Ways)
• Use Data Validation in Excel with Color (4 Ways)
• How to Use Data Validation List from Another Sheet (6 Methods)
• Excel VBA to Create Data Validation List from Array