In this tutorial, we have a dataset containing information about different products. The dataset includes details such as the Product Name, Size, Stock Status, and Price.
The objective is to create a system where we can select or enter a Product, Size, and Stock Status, and Excel will automatically return the corresponding Price if that particular combination exists in our data.
For example, if we select:
Product: Polo Shirt
Size: Medium
Stock Status: In StockExcel should search the dataset and return the price of that particular product.If the combination does not exist, Excel should display a message such as "No Record Found in Data."
Step 1: Create a Product Dropdown List
The first step is to create a dropdown list containing all the available products.To do this, select the cell where you want the Product dropdown list
Go to the Data tab in Excel and select Data Validation.
Inside the Data Validation window, select List from the Allow dropdown.
In the Source field, select the complete range containing your product names and then click OK.
Now, the selected cell will contain a dropdown list from which you can choose any product available in your dataset.
For example, your product list may contain items such as:
Belt
T-Shirt
Jeans
Polo Shirt
And other products
This makes it easier to select a product instead of manually typing its name.
Step 2: Create a Size Dropdown List
Next, we need to create a dropdown list for the Size.
Follow the same procedure.
Go to Data → Data Validation.
Select List.
In the Source field, select the cells containing your available sizes.
Select List.
In the Source field, select the cells containing your available sizes.
For example, the available sizes could be:
Small
Medium
Large
XL
Click OK.
Now you can select the required size directly from the dropdown list.
Step 3: Create a Stock Status Dropdown List
We also need to create a dropdown list for the Stock Status.
Again, go to:
Data → Data Validation → List
In the Source field, enter the two possible stock statuses:
In Stock, Out of Stock
After clicking OK, you will have a dropdown containing both options.
At this point, we have created three dropdown lists:
Product
Size
Stock Status
These three criteria will be used to find the correct price.
Step 4: Use XLOOKUP with Multiple Criteria
Now comes the most important part of the tutorial: using the XLOOKUP function to search for a price based on multiple conditions.
The idea is that we need to match three things simultaneously:
Product + Size + Stock Status
Once all three conditions match a row in our dataset, Excel should return the corresponding Price.
We can construct the lookup value by combining the three criteria using the & symbol.
The basic lookup value is:
Product&Size&Stock
This combines the three selected values into a single lookup condition.
For example, if:
Product = Polo Shirt
Size = Medium
Stock = In Stock
Excel effectively creates a combined lookup value based on these three values.
The same type of combination needs to be created for the data in our original dataset.
Step 5: Select the Product Column
In the XLOOKUP formula, the first column we need is the Product column.
Select the first product cell in your dataset.
If you want to select the entire list quickly, click on the first product and press:
Ctrl + Shift + Down Arrow
This will select all the consecutive product entries in that column.
After selecting the Product range, use the & symbol because we need to combine it with the next condition.
Step 6: Add the Size Column
Next, select the Size column.
Again, use the & symbol to combine the Product and Size values.
The lookup combination is now:
Product&SizeHowever, we still have one more condition to add.
Step 7: Add the Stock Status Column
Now select the Stock Status column and again use the & symbol.
The complete combination becomes:
Product&Size&Stock
This means Excel will only return a result when all three conditions match the same record.
This is particularly useful when the same product can have different sizes or different stock statuses.
Step 8: Select the Price Column
After defining the lookup criteria, we need to tell XLOOKUP what value should be returned.
In this case, we want Excel to return the Price.
Therefore, select the Price column as the return array.
The overall formula structure is:
=XLOOKUP(Product&Size&Stock, DataProduct&DataSize&DataStock, Price, "No Record Found in Data")
The exact cell references will depend on how your worksheet is structured.
The important concept is:
Lookup Value: Product + Size + Stock Status
Lookup Array: Product Column + Size Column + Stock Status Column
Return Array: Price Column
If Not Found: "No Record Found in Data"
Step 9: Add a Not-Found Message
The XLOOKUP function allows us to specify what should happen when no matching record is found.
For example, we can use:
"No Record Found in Data"
This is useful because instead of displaying an Excel error such as #N/A, the worksheet will display a clear and understandable message.
You can customize this message according to your requirements.
For example, you could use:
"Product Not Available"
or:
"No Matching Record Found"
The message can be whatever you prefer.
Step 10: Test the Formula
Now let's test the result.
Suppose we select:
Product: Polo Shirt
Size: Medium
Stock: In Stock
If that combination exists in the dataset, XLOOKUP will find the matching row and return its price.
For example, the result might be:
1147
This confirms that the formula is successfully matching all three conditions.
Step 11: Change the Product
Now change the selected product.For example, suppose we select Jeans while keeping the other criteria unchanged.
If there is no record in the dataset matching the selected:
Jeans + Size + Stock Status
then Excel will display:
No Record Found in Data
This is exactly what we want because the formula is checking the complete combination rather than searching for the product alone.
Step 12: Change the Stock Status
We can also change the Stock dropdown from In Stock to Out of Stock.
Excel will automatically perform the lookup again.
If a matching record exists under the Out of Stock category, its price will be returned.
If no matching record exists, the formula will display:
No Record Found in Data
Similarly, you can change the size from Medium to Small, Large, or XL and Excel will immediately
search for the corresponding combination.
Why This Method Is Useful
The major advantage of this approach is that we can search a large dataset using multiple criteria simultaneously.
Instead of creating separate formulas for every possible combination, we can create three dropdown lists and use a single XLOOKUP formula.
The user simply selects:
Product → Size → Stock Status
and Excel automatically returns the relevant Price.
This method is especially useful for:
Product inventory systems
Stock management
Price lookup sheets
Sales reports
Product catalogs
Inventory dashboards
Order-entry systems
Excel-based billing systems
Final Formula Structure
The general structure of the formula is:=XLOOKUP(Product&Size&Stock, Product_Range&Size_Range&Stock_Range, Price_Range, "No Record Found in Data")
Here, the & operator combines multiple criteria so that XLOOKUP can search for a unique combination.
For example, the lookup can be based on:
Polo Shirt + Medium + In Stock
This combination is then matched against the corresponding Product, Size, and Stock columns in the dataset.
Therefore, XLOOKUP can be used to find a result based on multiple criteria, even when the dataset contains a large number of products.
The key takeaway is that by combining Data Validation dropdowns with XLOOKUP, we can create a simple and interactive product-price lookup system in Excel.
Once the dropdowns and formula are set up, changing the Product, Size, or Stock Status automatically updates the result.
This makes the process faster, more accurate, and much easier to use compared with manually searching through a large dataset.
Step 1: Product की Drop-down List बनाएं
सबसे पहले हमें Product की एक Drop-down List बनानी है।
इसके लिए:
- Excel में Data Tab पर जाएं।
- Data Validation पर Click करें।
- Data Validation के अंदर Allow में List Select करें।
- अब Source में अपने सभी Products की Range Select करें।
- इसके बाद OK पर Click करें।
अब Product वाले Cell में Drop-down List बन जाएगी।
इसमें आप अपने Products जैसे:
- Polo Shirt
- T-Shirt
- Jeans
- Belt
आदि Select कर सकते हैं।
Step 2: Size की Drop-down List बनाएं
अब हमें Size के लिए Drop-down बनानी है।
इसके लिए:
- Size वाले Cell को Select करें।
- Data → Data Validation पर जाएं।
- Allow → List Select करें।
- Source में Size वाली पूरी List Select करें।
- OK पर Click करें।
अब आप Size में आसानी से:
- Small
- Medium
- Large
- XL
Select कर सकते हैं।
Step 3: Stock की Drop-down List बनाएं
अब Stock के लिए भी इसी तरह Data Validation लगानी है।
Data Validation में जाकर Allow → List Select करें।
Source में दो Options रखें:
In Stock, Out of Stock
अब Stock वाले Cell में दो Options दिखाई देंगे:
- In Stock
- Out of Stock
इस तरह हमारे तीनों Input तैयार हो गए:
Product + Size + StockStep 4: XLOOKUP Formula का इस्तेमाल करें
अब हमें इन तीनों चीजों के आधार पर Price निकालना है।
इसके लिए हम XLOOKUP Function का इस्तेमाल करेंगे।
Formula का Logic होगा:
Product + Size + Stock → Price
यहां हम तीनों Criteria को एक साथ जोड़कर Search करेंगे।
Step 5: Product की पूरी Range Select करें
अब Formula लगाते समय सबसे पहले Product की पूरी Data Range Select करनी है।
इसके लिए:
- Product की पहली Data Cell पर Click करें।
- Ctrl + Shift + Down Arrow दबाएं।
इससे Product की पूरी List Select हो जाएगी।
अब इसके बाद & का Symbol लगाना है।
यह Symbol Excel में अलग-अलग Values को जोड़ने के लिए इस्तेमाल होता है।
Step 6: Size को जोड़ें
अब Product के बाद Size की Range Select करनी है।
फिर से & Symbol लगाएं और Size वाले पूरे Data को Select करें।
अब हमारा Logic हो गया:
Product + Size
इसके बाद फिर से & लगाना है।
Step 7: Stock को जोड़ें
अब Stock वाली Range Select करें।
इस तरह हमारे पास तीनों Criteria एक साथ हो जाएंगे:
Product + Size + Stockयही तीनों चीजें Excel में Match की जाएंगी।
Step 8: Price की Range Select करें
अब हमें Excel को बताना है कि Match मिलने के बाद कौन-सी जानकारी वापस दिखानी है।
इसके लिए Price वाली पूरी Range Select करें।
अगर Product, Size और Stock तीनों Match हो जाते हैं, तो Excel उसी Row का Price दिखाएगा।
Step 9: अगर Data नहीं मिले तो Message दिखाएं
XLOOKUP में हम यह भी बता सकते हैं कि अगर कोई Record नहीं मिलता है तो क्या Message दिखाना है।
उदाहरण के लिए:
No Record Found in Data
इसकी जगह आप अपनी पसंद का कोई भी Message लिख सकते हैं।
जैसे:
- Price Not Found
- Product Not Available
- No Record Found
Step 10: Formula Enter करें
अब Formula पूरा करने के बाद Enter दबाएं।
Excel आपके द्वारा Select किए गए:
Product + Size + Stock
के आधार पर Data में Match ढूंढेगा।
अगर Match मिल गया, तो उसका Price दिखाई देगा।अगर Match नहीं मिला, तो आपका दिया हुआ No Record Found in Data वाला Message दिखाई देगा।
Example 1: Record मिल गया
मान लीजिए हमने Select किया:
Product: Polo Shirt
Size: Medium
Stock: In Stock
अगर Data में यह Combination मौजूद है और उसका Price 1147 है, तो Result में:
1147
दिखाई देगा।
Example 2: Record नहीं मिला
अब मान लीजिए हमने Select किया:
Product: Jeans
Size: Medium
Stock: In Stock
अगर इस Combination का कोई Record Data में नहीं है, तो Result में:
No Record Found in Data
दिखाई देगा।
Example 3: Stock बदलकर Check करें
अब अगर हम Stock को Out of Stock कर देते हैं, तो XLOOKUP फिर से Check करेगा:
Product + Size + Out of Stock
अगर इसका Record मौजूद है, तो Price दिखाई देगा।
अगर Record मौजूद नहीं है, तो:
No Record Found in Data
दिखाई देगा।
इस पूरे Method में हम केवल Product के आधार पर Price नहीं निकाल रहे हैं।
हम तीन Conditions को एक साथ Match कर रहे हैं:
Product + Size + Stockऔर इन तीनों का सही Combination मिलने पर हमें Price मिल जाता है।
XLOOKUP का आसान Logic
मान लीजिए:
Product = Polo Shirt
Size = Medium
Stock = In Stock
Excel इन तीनों को एक साथ लेकर Data में Search करेगा।
जिस Row में:
Polo Shirt + Medium + In Stock
का Match मिलेगा, उसी Row से Price वापस आएगा।
इस Method का फायदा
इस तरीके से हम XLOOKUP की मदद से Multiple Criteria के आधार पर Data Find कर सकते हैं।
उदाहरण:
Product + Size + Stock → Price
इसी तरह जरूरत पड़ने पर और भी Criteria जोड़े जा सकते हैं।
जैसे:
Product + Size + Color + Stock → Price
या:
Product + Brand + Size + Stock → Price