Find Price by Product Name (Case Insensitive)
As a small business owner, I often need to quickly look up the price of a product for a customer or to update inventory. My product list might have inconsistent capitalization (e.g., 'Widget A' vs 'widget a'), and I need a reliable way to find the price even if my search term's capitalization doesn't exactly match the database.
=XLOOKUP(LOWER(D2), LOWER(A2:A5), B2:B5, "Product Not Found", 0) How it works: This formula works by converting both your search term (`D2`) and the entire lookup range of product names (`A2:A5`) to lowercase using the `LOWER()` function. This creates a temporary, case-insensitive comparison environment for XLOOKUP. `XLOOKUP` then performs an exact match (`0`) on these lowercase values, returning the corresponding price from the `B2:B5` range. If no match is found, it will display 'Product Not Found', preventing errors.
Data Setup
| Product Name | Price | Search Product | Result |
|---|---|---|---|
| Widget A | 10.50 | widget a | 10.50 |
| Gadget B | 25.00 |
Step-by-Step Guide
Prepare your product data: Ensure your product names are in one column (e.g., A2:A5) and their corresponding prices in an adjacent column (e.g., B2:B5).
Enter the product name you want to search for (e.g., 'widget a') into a designated search cell (e.g., D2).
In the cell where you want the price to appear (e.g., E2), type the formula: `=XLOOKUP(LOWER(D2), LOWER(A2:A5), B2:B5, "Product Not Found", 0)`
Press Enter. The formula will return the price of the product, regardless of the capitalization used in your search term or the original product list.
Explore More
Summarizing Expenses by Category
As a small business owner, I track all my expenses in an Excel sheet. At the end of the month, I need to know the total spent on specific categories like 'Utilities' or 'Office Supplies' for budgeting and tax purposes. Manually adding these up is tedious and error-prone.
Determining Monthly Payments for New Equipment Financing
A small business owner needs to finance new equipment to expand operations and wants to calculate the fixed monthly payment required for the lease, given the equipment's total cost, the annual interest rate, and the lease term in years, to manage cash flow effectively and make informed investment decisions.
Calculate Total Sales by Product Category
I need to sum up the total revenue generated specifically from the 'Electronics' category in my sales log.
Calculate Equipment Lease Monthly Cost
I need to figure out the monthly payment for a $15,000 piece of machinery leased over 3 years at 5% interest.