'%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 Fill Missing Vendor Names in Excel Using INDEX and MATCH
Question details
The user needs a formula to fill blank cells in a vendor name column by matching document numbers from another column to retrieve the correct vendor name.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating and cleaning spreadsheet data where some rows are missing vendor names but share a common document number with fully populated rows.
- Observed behavior
- The vendor names column contains blank cells that need to be populated dynamically using non-blank values from other rows associated with the same document number.
Ensure your document numbers are formatted consistently as either text or numbers, and remove any trailing spaces using the TRIM function to prevent match errors.
Use INDEX and MATCH with Exact Match Parameter
Create a helper column using an INDEX and MATCH formula with an exact-match argument (0) to pull the correct vendor names, then paste the results over the original blanks.
The INDEX and MATCH combination is ideal for this scenario. By setting the MATCH function's final argument to 0, you force Excel to find an exact match for your document number, ensuring accuracy even if the data is unsorted.
Click on the column header next to your data, such as Column Z (or any empty column), to use it as a temporary workspace for your formulas.
Select the second cell of your helper column (e.g., Z2) and type the formula: =INDEX($Y:$Y,MATCH(D2,$D:$D,0)). Press Enter to confirm.
Select cell Z2, hover over the small square at the bottom-right corner until your cursor becomes a crosshair, and double-click or drag it down to apply the formula to all rows.
Highlight all the newly calculated cells in Column Z, press Ctrl+C to copy, then right-click on cell Y2 (the top of your original vendor column). Choose 'Paste as Values' (the clipboard icon with 123) to permanently replace the formulas with the actual text.
Easily Organize and Clean Data with WPS Spreadsheet
WPS Spreadsheet handles complex formulas like INDEX and MATCH flawlessly, allowing you to clean your datasets and fill in missing information in seconds. It is a highly efficient and fully compatible tool for all your data management needs.
- 1. Open your spreadsheet: Launch WPS Office, click on 'Spreadsheet', and open your document containing the incomplete vendor data.
- 2. Apply the lookup formula: In an empty column, type your INDEX and MATCH formula to dynamically look up the matching document numbers and extract the vendor names.
- 3. Paste as Values: Copy your formula results, right-click your original vendor column, and select 'Paste Special' > 'Values' to overwrite the empty cells seamlessly.
Frequently Asked Questions
Why does the MATCH formula return an #N/A error?
The #N/A error means Excel cannot find the lookup value. This happens if the document number is truly missing from the lookup column, or if there is a mismatch in data types (e.g., one is formatted as text and the other as a number). It can also be caused by hidden spaces.
What happens if all rows with the same document number have blank vendor names?
If there is no non-blank vendor name associated with that specific document number anywhere in the column, the INDEX and MATCH formula will return a 0 or a blank result. You must have at least one completed record per document number for the formula to pull the data.
Can I use XLOOKUP instead of INDEX and MATCH to fill the blanks?
Yes, if you are using a recent version of Excel or WPS Spreadsheet, you can use XLOOKUP. The equivalent formula would be =XLOOKUP(D2,$D:$D,$Y:$Y). XLOOKUP defaults to an exact match, making it slightly simpler to write than INDEX and MATCH.
