How to Fill an Excel Cell Based on Multiple Selections Using Formulas
Question details
The user needs an Excel formula to populate a specific cell by evaluating combinations of values from three different columns against a lookup table on another worksheet. One of the columns requires a partial text match of only the first three characters.
- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Automating data entry by retrieving a specific output value from a reference table based on multiple dynamic dropdown selections and inputs.
- Observed behavior
- The goal is to accurately match three distinct criteria—including a partial string match—and return a value without resorting to long, difficult-to-maintain nested IF formulas.
Ensure that your reference data on the secondary worksheet is organized in a clear, tabular format. It is highly recommended to convert your lookup range into an official Table format to make your formulas dynamic and easier to read.
Use XLOOKUP with Boolean Logic and Partial Matching
XLOOKUP easily handles multiple criteria by multiplying boolean arrays, making it cleaner and more flexible than traditional lookup functions. It also supports text manipulation functions natively.
By multiplying conditions together (e.g., Condition1 * Condition2), Excel creates an array of 1s (TRUE) and 0s (FALSE). XLOOKUP can then search for the number 1 to find the row where all conditions are met simultaneously.
Click on the cell (e.g., in Column D) where you want the final matched result to appear.
Begin your formula by typing =XLOOKUP(1, to tell Excel you are looking for a row where all multiplied boolean conditions evaluate to TRUE (represented as 1).
Enter your criteria wrapped in parentheses and multiply them. Incorporate the LEFT function for the partial match. Example: =XLOOKUP(1, (Sheet2!$E$2:$E$100=A2) * (Sheet2!$F$2:$F$100=B2) * (LEFT(Sheet2!$G$2:$G$100, 3)=LEFT(C2, 3)),
Highlight the range containing the values you want to return (e.g., Column J on Sheet2), add a closing parenthesis, and press Enter: =XLOOKUP(1, (Sheet2!$E$2:$E$100=A2) * (Sheet2!$F$2:$F$100=B2) * (LEFT(Sheet2!$G$2:$G$100, 3)=LEFT(C2, 3)), Sheet2!$J$2:$J$100)
Use INDEX and MATCH with Concatenation for Older Versions
If you are using an older spreadsheet version that does not support the XLOOKUP function, combining INDEX and MATCH with an array formula is the best alternative.
Easily Handle Complex Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, XLOOKUP, and text manipulation functions like LEFT, allowing you to build dynamic lookup tables with ease. It perfectly mirrors the functionality you expect for handling multiple criteria lookups.
- 1. Open your workbook: Launch WPS Spreadsheet and open the .xlsx or .xls file containing your primary data and lookup tables.
- 2. Enter your lookup formula: Select your target cell and use the =XLOOKUP() function just as you would normally, passing multiple criteria using boolean multiplication.
- 3. Utilize text functions seamlessly: Nest the LEFT(), RIGHT(), or MID() functions directly inside your criteria arrays to match specific character lengths without errors.
- 4. Apply to the entire column: Press Enter to execute the formula, then simply drag the fill handle at the bottom-right of the cell to auto-fill the rest of your column.
Frequently Asked Questions
Can I use VLOOKUP with multiple criteria instead of XLOOKUP?
Standard VLOOKUP is designed for a single lookup value. To use multiple criteria with VLOOKUP, you would need to create a new 'helper column' in both your source data and your lookup table that concatenates the criteria together. Because of this extra step, XLOOKUP or INDEX/MATCH are generally better suited for multiple conditions.
Why is my lookup array formula returning an #N/A error?
An #N/A error typically means no exact match was found. Check that your lookup data does not have trailing spaces, ensure your partial match criteria (like the LEFT function) is extracting the correct number of characters, and verify that all criteria arrays and the return array are exactly the same size.
Are nested IF statements better than lookup tables for multiple conditions?
No. While nested IF statements work for a very small number of static conditions, they become extremely difficult to read, troubleshoot, and update. A structured lookup table combined with an XLOOKUP formula is much more efficient and easier to manage as your business rules or data grow.
How does multiplying conditions in XLOOKUP work?
When you evaluate a condition like (A1:A10="Apple"), Excel creates an array of TRUE/FALSE values. In Excel formulas, multiplying TRUE by TRUE equals 1, while any multiplication involving FALSE equals 0. By searching for the value 1, XLOOKUP finds the specific row where all multiplied conditions were TRUE.
