# How to Use Custom VLOOKUP Formula in Excel Data Validation

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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:
`=VLOOKUP(D11,B5:D9,2,0)`
• Then press the Enter button to get the output.

Now weâ€™ll find the sales in Cell D13.

• Type the following formula in it:
`=VLOOKUP(D11,B5:D9,3,0)`
• 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.

### 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:
`=AND(C11>=VLOOKUP(B11,B5:D8,2,0),C11<=VLOOKUP(B11,B5:D8,3,0))`
• 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 the 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

## 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

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

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF