'%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 Return Product Height Based on Selected Size in Excel
Question details
The user needs an Excel formula to retrieve a product's height based on a selected size from a horizontally arranged dataset.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up a corresponding value from a horizontal dataset where product sizes are in one row and heights are in the row directly below.
- Observed behavior
- The formula needs to take the selected size in cell B4, search row 2 on the data sheet, and return the corresponding height from row 3 into cell B8.
Ensure your source dataset is organized with sizes in a single row (e.g., Row 2) and the corresponding heights in the row immediately below it (e.g., Row 3).
Use the XLOOKUP Function (Modern Excel Versions)
XLOOKUP is the most robust and flexible formula for horizontal and vertical lookups in modern versions of Excel.
If you are using Excel 2021 or Microsoft 365, XLOOKUP is the most efficient way to search for a value in one row and return a corresponding value from another row.
Click on cell B8 where you want the product height to be displayed.
Type =XLOOKUP(B4,'Data Sheet'!$B$2:$P$2,'Data Sheet'!$B$3:$P$3,"") into the formula bar and press Enter.
Change the selected product size in cell B4 to ensure the corresponding height in cell B8 updates automatically.
Use the HLOOKUP Function (Older Excel Versions)
If you are using an older version of Excel that does not support XLOOKUP, HLOOKUP combined with IFERROR is the best alternative.
Use WPS Spreadsheet for Advanced Formula Lookups
WPS Spreadsheet fully supports both XLOOKUP and HLOOKUP functions, making it incredibly easy to retrieve data from horizontal or vertical ranges. It is a completely free and fully compatible alternative for all your data processing needs.
- 1. Install WPS Office: Download and install WPS Office on your device.
- 2. Open your spreadsheet: Launch WPS Spreadsheet and open your existing workbook.
- 3. Enter the formula: Select cell B8 and input your preferred XLOOKUP or HLOOKUP formula.
- 4. Get the result: Press Enter to instantly get your corresponding product height.
Frequently Asked Questions
Why does my HLOOKUP formula return an #N/A error?
This usually happens when the lookup value (selected size) does not exactly match any value in the top row of your data range. Check for leading or trailing spaces in your cells, or wrap your formula in the IFERROR function to hide the error.
Can I use VLOOKUP instead of HLOOKUP for this scenario?
No, VLOOKUP is designed for vertical data where lookup values are located in columns. Since your product sizes are organized horizontally in a single row, you must use HLOOKUP or XLOOKUP.
Is XLOOKUP better than HLOOKUP?
Yes, XLOOKUP is more flexible and less prone to breaking if you insert or delete rows in your dataset. It can look both horizontally and vertically, and it features built-in error handling without needing an additional IFERROR wrapper.
