'%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 Use Excel Formulas to Find All Rows Containing an Exact Value
Question details
The user needs a spreadsheet formula to find the specific row numbers that contain an exact matching value, either returning just the first instance or extracting a list of all matching rows.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Searching through a dataset to locate specific data entries by returning their exact row numbers using a dynamic cell reference.
- Observed behavior
- The user wants to input a target value (such as a cell reference like HV4) and output either the first matching row number or a comprehensive array of all row numbers containing that exact value without manual filtering.
Ensure your lookup column does not contain unintended leading or trailing spaces, as these hidden characters can prevent lookup formulas from identifying exact matches.
Find the First Exact Match Using the MATCH Function
Use the MATCH function when you only need to locate the row number for the very first instance of a specific value in a column.
The MATCH function searches for a specified item in a range of cells and then returns the relative position of that item. By setting the match type to FALSE (or 0), you force the formula to look for an exact match only.
Click on the empty cell where you want the row number to be displayed.
Type the formula =MATCH(HV4, B:B, FALSE) into the cell or the formula bar. Replace 'HV4' with the cell containing your search value, and 'B:B' with the column you want to search.
Press the Enter key. The cell will now display the row number of the first exact match. If no match is found, it will return an #N/A error.
Return All Matching Row Numbers Using the FILTER Function
Use the FILTER function combined with the ROW function to extract a complete list of every row containing your exact lookup value.
Easily Filter and Search Data in WPS Spreadsheet
WPS Spreadsheet fully supports advanced lookup functions like MATCH and dynamic array formulas like FILTER. It allows you to efficiently search large datasets, extract exact row numbers, and analyze your information seamlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing the data you want to search.
- 2. Select your output cell: Click on the blank cell where you want the formula results to appear.
- 3. Enter the lookup formula: Type your preferred formula, such as =MATCH(HV4, B:B, FALSE) for a single result or =FILTER(ROW(B:B), B:B=HV4) for multiple results.
- 4. Press Enter to calculate: Hit Enter on your keyboard. WPS Spreadsheet will immediately process the function and display the matching row numbers.
Frequently Asked Questions
Why does my MATCH formula return #N/A even when the exact value exists?
This error typically occurs due to hidden formatting mismatches, such as trailing spaces, invisible characters, or numbers formatted as text. You can use the TRIM function to remove extra spaces or ensure both the lookup cell and the target range share the same data format.
Can I search for a partial text match instead of an exact value?
Yes. When using the MATCH function, you can incorporate wildcard characters like the asterisk (*). For example, entering =MATCH("*"&HV4&"*", B:B, 0) will search for any row in column B that contains the text located in cell HV4.
Is the FILTER function available in older spreadsheet software?
The FILTER function relies on modern dynamic array architecture. If you are using an older version of Excel or a spreadsheet program that does not support dynamic arrays, this formula will return an error. In such cases, you must use more complex INDEX and AGGREGATE array formulas to list multiple matches.
