Excel Formula to Find Decimal Values Across Rows and Columns
Question details
The user needs a formula to look up specific decimal values (such as 10.0 through 19.9) across a table with multiple rows and columns to return a corresponding result.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching for a specific intersection value in a 2D matrix by splitting a decimal number into row and column lookup criteria.
- Observed behavior
- The user wants to successfully return the intersecting cell value from a range by matching the integer and fractional parts of a decimal number against row and column headers.
Ensure your source data table has clear row and column headers that correspond to the integer and fractional parts of the decimal values you want to look up, and check that your target cell formats match the expected output.
Use INDEX and MATCH with String Manipulation
This method splits the decimal number into its integer and decimal parts to find the exact row and column intersection using INDEX and MATCH.
By utilizing the MID, FIND, and IFERROR functions inside a MATCH function, you can dynamically extract the numbers before and after the decimal point. This allows the INDEX function to pinpoint the exact cell in a 2D range.
Click on the target cell where you want the lookup result to appear (for example, cell P3).
Type the following formula: =INDEX($B$3:$K$12,MATCH(IFERROR(--MID(O3,FIND(".",O3)+1,LEN(O3)),0),--$A$3:$A$12,0),MATCH(IFERROR(--MID(O3,1,FIND(".",O3)-1),O3),--$B$2:$K$2,0))
Press Enter to execute the formula, then click and drag the fill handle down to apply this lookup logic to other cells in the column.
Use the FILTER Function for Dynamic Arrays
A modern approach for newer versions of Excel utilizing the FILTER function to extract the intersection based on integer and decimal extraction.
Look Up Complex Matrix Values Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, including INDEX, MATCH, and FILTER, making it simple and efficient to search for decimal values across complex rows and columns.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data matrix.
- 2. Format headers correctly: Ensure your matrix table is organized with integer headers for the columns and decimal headers for the rows.
- 3. Enter the lookup formula: Select an empty cell and input the exact same INDEX and MATCH or FILTER formula used in Excel.
- 4. Retrieve the result: Press Enter to instantly fetch the correct intersecting decimal value seamlessly.
Frequently Asked Questions
Why does my INDEX and MATCH formula return an #N/A error?
An #N/A error usually means the exact integer or decimal part extracted from your lookup value doesn't exist in your row or column headers. Ensure your headers exactly match the data types (text vs. numbers) of your extracted formula values.
How can I convert a text result to a number in my Excel formula?
You can convert a text-formatted number back to a numeric value by adding a double unary minus (--) right after the equals sign, or by wrapping the entire formula in the VALUE() function.
What does the FIND function do in this lookup formula?
The FIND(".", O3) function locates the exact character position of the decimal point within your target number. This allows the MID or RIGHT functions to accurately isolate and split the integer part from the fractional part for the 2D lookup.
