'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Perform a Two-Way Lookup by Date and Product Name in Excel
Question details
The user needs to retrieve a specific value from a data grid by matching a date in a table column and a product name in the header row.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up a data point at the intersection of a specific row (date) and column (product name) within a two-dimensional matrix.
- Observed behavior
- A combination of formulas is required to dynamically identify the correct row and column and extract the intersection value.
Ensure that your row values (dates) and column headers (product names) use consistent formatting and exactly match your search criteria to avoid lookup errors.
Use INDEX and MATCH for a Two-Way Lookup
The most flexible and robust method for two-way lookups, retrieving the exact intersection of a specific row and column.
The INDEX function returns a value from a specified grid based on a row and column number. By nesting two MATCH functions inside INDEX, you can dynamically find the correct row number for the date and the correct column number for the product name.
Click on an empty cell where you want the matched result to appear.
Input the formula `=INDEX(B5:X100, MATCH(B1, A5:A100, 0), MATCH(B2, B4:X4, 0))`. In this example, B5:X100 is your data grid, B1 is the target date, A5:A100 is the date column, B2 is the target product name, and B4:X4 is the product header row.
Press Enter. The first MATCH will locate the date's row, the second MATCH will locate the product's column, and INDEX will extract the value precisely at their intersection.
Use VLOOKUP and MATCH for a Two-Way Lookup
An alternative approach that combines VLOOKUP for row searching with MATCH for dynamic column index selection.
Easily Perform Complex Lookups in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, including INDEX, MATCH, and VLOOKUP. You can flawlessly execute two-way lookups and analyze complex data matrices with ease.
- 1. Open your file: Launch WPS Spreadsheet and open the document containing your data matrix.
- 2. Use the Function Wizard: Select the result cell, click the 'fx' icon next to the formula bar, and search for the INDEX function.
- 3. Nest the MATCH functions: Within the INDEX arguments box, nest your MATCH functions to dynamically define the required row and column criteria.
- 4. Calculate and verify: Press Enter or click OK to apply the formula and immediately retrieve your two-way lookup value.
Frequently Asked Questions
Why does my INDEX and MATCH formula return an #N/A error?
An #N/A error usually happens if the lookup value (the date or product name) does not perfectly match the data in your table. Check for trailing spaces, typos, or inconsistent date formats (e.g., text vs. date values) between your lookup cells and the source table.
How do I make the formula ranges absolute so they don't shift when copied?
Highlight the range references in your formula bar (like A5:A100) and press the F4 key. This will add dollar signs (e.g., $A$5:$A$100), making the reference absolute so it won't move when you drag the formula to other cells.
Can I use XLOOKUP for a two-way lookup instead?
Yes, if your spreadsheet software supports it, you can nest two XLOOKUP functions to achieve the same result. The syntax is `=XLOOKUP(B1, A5:A100, XLOOKUP(B2, B4:X4, B5:X100))`. The inner XLOOKUP returns the matching column, and the outer XLOOKUP finds the matching row.
