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.


Download Practice Workbook

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.

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

  • 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.

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

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

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

  • The range is selected successfully, just press OK.

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

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.

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

  • 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.

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

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.

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


Similar Readings:


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.

Apply Dynamic Data Validation with Multiple VLOOKUP Formula

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

Apply Dynamic Data Validation with Multiple VLOOKUP Formula

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

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo