OmniDocVLOOKUP searches multiple columns and multiple results: returns multiple values.
Explain the two ways to return multiple columns of data in Excel and list all matching rows in one search: use the column number parameter of VLOOKUP with MATCH to retrieve multiple columns, and use FILTER or auxiliary columns to list multiple results, with an item-by-item verification method.
Key points of this article
- The third parameter of VLOOKUP determines which column to return. Use MATCH to automatically calculate the column number based on the table header. Drag the formula to the right to retrieve multiple columns at once.
- VLOOKUP stops when it finds a match. To list all matching rows, you need a dynamic array function such as FILTER, or use an auxiliary column to number the duplicates.
- The query column must be at the left end of the search area, otherwise VLOOKUP cannot be used and XLOOKUP or INDEX plus MATCH should be used instead.
- Finally, be sure to add the exact matching parameter FALSE and check the number of rows returned to avoid approximate matching from bringing erroneous data into the results.
When many people use VLOOKUP to check data, they can only get one result at first: enter the employee number and return the name, but the department and phone number have to write the formula again. Another situation is more troublesome: there are more than a dozen people in the same department, VLOOKUP will only return one, and the rest are silently ignored. This article is written for people who often do queries, checks, and summarizations in Excel. It explains two things clearly: how to use a search formula to return multiple columns of data, and how to list all matching results.
After reading this, you should be able to: In an employee table, enter a job number and get the name, department and phone number at the same time; enter a department name, and all the personnel in the department will be listed. The two things are done differently. If you choose the wrong one, you will miss data, so let’s clarify the situation first.
How to write a formula to find multiple columns and results? One sentence conclusion: use VLOOKUP plus MATCH for multiple columns, and use FILTER or auxiliary columns for multiple results.
VLOOKUP only returns the value of one column in the search area at a time, but its column number parameter can be written as a formula, so multi-column problems can be solved with MATCH. Listing multiple matching lines is another matter. VLOOKUP itself cannot do this and requires a different tool.
| your situation | Recommended practices | reason |
|---|---|---|
| Each query value only corresponds to one row, and multiple columns need to be fetched at the same time. | VLOOKUP works with MATCH to calculate column numbers | The formula structure remains unchanged, just drag it to the right. It is compatible with older versions. |
| The same value corresponds to multiple rows, all must be listed | FILTER function | The results are automatically spread downwards and can be solved with one formula, but a newer version is required. |
| The version is older and does not have dynamic array functions. | Auxiliary column number plus INDEX and MATCH | Each duplicate item has a unique number, and common versions can be used. |
| The query column is not at the left end of the search area | XLOOKUP or INDEX plus MATCH | VLOOKUP requires the query column to be on the left. Other writing methods do not have this restriction. |
| Only one-time results required, no formula required | Filter or PivotTable | No need to write formulas, suitable for one-time sorting |
Before you start: Confirm these points first
Before you start, look at four things. They determine whether the subsequent writing method can be used.
First, does your Excel version support dynamic arrays. FILTER and some new functions are only available in newer versions. Enter "=FILTER" in the cell. If an automatic prompt appears, it means it is available; if not, use the auxiliary column method.
Second, query whether the column is at the left end of the table. VLOOKUP can only search from the left end of the search area. If the job number is in column D and the name is in column A, VLOOKUP cannot be used directly, and XLOOKUP or INDEX plus MATCH should be used instead.
Third, whether the header text is consistent. When using MATCH to find column numbers, the table header must be exactly the same as the text quoted in the formula. If there is one more space and one less "", an error will be reported. It is recommended to use "Find and Replace" or manually check the header to confirm that there are no extra spaces.
Fourth, whether there are duplicate values in the source data. If a job number appears repeatedly, it means there may be a problem with the data, and VLOOKUP will only return one. If there are multiple people in the same department, it is a normal multiple results and needs to be processed according to the second method below.
Steps to use VLOOKUP to return multiple columns at once
Suppose an employee table is placed in columns A to E. A1 to E1 are "employee number, name, department, phone number, and email address" in order, and the data starts from row 2. We enter the employee number in G2 and write the field names we want to check "name, department, phone number" in H1 to J1.
- Enter the formula
=VLOOKUP($G2,$A$2:$E$500,MATCH(H$1,$A$1:$E$1,0),FALSE)in H2. The first parameter is the query value, the second parameter is the search area, the third parameter uses MATCH to automatically calculate the column number based on the table header, and the last FALSE indicates an exact match. - Check the function of the MATCH part: it finds the position of "name" in A1 to E1 and returns 2. This number is the column number to be returned by VLOOKUP.
- Drag the formula of H2 to the right to J2, and all three columns will get the correct column numbers. Because the table header is changing, the columns referenced by the formula will also change accordingly, and there is no need to modify them one by one.
- Enter a job number in G2, such as an actual job number, and confirm that the corresponding name, department and phone number appear in H2 to J2.
- If a column displays #N/A, it means that the query value cannot be found in the source table, or the header text is inconsistent. Go back to the header check in the first step.
- The scope of the search area must cover all data. The above $A$2:$E$500 means up to the 500th row. If there is more data, the range needs to be expanded, or the entire column reference needs to be used instead.
- After confirmation, if you want to query other job numbers, you only need to modify G2, and the three column results will be updated simultaneously.
Use FILTER to list all matching lines
When there are multiple people in the same department and they all need to be listed, VLOOKUP cannot do that. If the version supports it, you can use FILTER. Suppose you want to enter the department name in G2, and the result will list the job numbers, names and phone numbers of all employees in the department starting from G5. You can enter =FILTER(A2:E500,C2:C500=G2,"没有匹配结果") in G5.
The meaning of this formula is: in A2 to E500, filter out all rows where column C is equal to G2 and return all columns. The results are automatically spread downwards, and the number of rows changes with the number of matches. If there is no match, the prompt text in quotation marks will be displayed instead of reporting an error.
There are two points to note when using it. There must be no content below and to the right of the result area, otherwise a #SPILL! error will appear, indicating that the result is blocked. In addition, if you only need a few of the columns, replace A2:E500 with the required columns. For example, if you only return the name and phone number, you can write FILTER(B2:B500,...) plus column D, or use functions such as CHOOSECOLS, but you must first confirm the version support.
How to do it in the old version: Auxiliary column numbering method
If there is no FILTER, you can use auxiliary columns to turn duplicates into non-repeating keywords, and then look for them one by one. Enter =C2&COUNTIF($C$2:C2,C2) in F2 and fill it down. In this way, the same department gets "sales Department 1" when it appears for the first time, "sales Department 2" for the second time, and so on.
When querying, enter the department name in G2, enter =IFERROR(INDEX($B$2:$B$500,MATCH($G$2&ROWS(H$2:H2),$F$2:$F$500,0)),"") in H2, drag down, and return each name of that department in turn. To return multiple columns, replace the area of the INDEX with the corresponding column and copy the formula on the right. This method is more step-by-step, but common versions can be used.
# check item by item after completion
After finding out the results, do not use them directly, check the following items first.
| Check item | What do you think? | What if there's a problem? |
|---|---|---|
| Whether the returned column is correct | Select two job numbers at random and check the source table to see if the name, department and phone number match. | Check whether the header is consistent and whether the range of MATCH starts from A1 |
| Whether or not # NCMA appears | Scan the result area to see if there is an error value | Confirm whether the query value exists, whether there are extra spaces or full-width differences |
| Is the number of rows complete? | Use COUNTIF to count the number of people in this department in the source table and compare it with the number of rows of FILTER results. | Expand the search area to make sure there are no missing lines |
| Is there a duplicate value? | Use COUNTIF to check duplicates on the work number column | Clean up the duplicate data and query it again |
| Is the result truncated? | See if there is any content blocking the bottom of the FILTER result. | Clear the blocked cell, or change to a starting position |
# Core comparison
Each of the three methods has its own applicable scenarios, which can be judged in the following way.
| Method | Can multiple columns be returned? | Can you list multiple lines | Version requirement | Main risk |
|---|---|---|---|---|
| VLOOKUP plus MATCH | Can | No, only one is returned | Common versions support it. | The query column must be at the left end, leaving out duplicates |
| FILTER | Can | Can | Newer version | As a result, the area was occupied and the Times was wrong. |
| Auxiliary column plus INDEX and MATCH | Yes, you need to copy it column by column | Can | Common versions support it. | There are many steps and the auxiliary column needs to be maintained. |
# which method is suitable for you?
If you just make a standard information card, enter a number at a time and look at the corresponding information. It is more direct to use VLOOKUP plus MATCH. If you often need to pull out all the details by category, such as the list of personnel in each department, FILTER will save a lot of dragging. If a colleague's computer version is older and the file should be shared with others, the auxiliary column method is safer because it can be opened and calculated normally in common versions.
# frequently asked questions (FAQ)
1. Can VLOOKUP return the whole row of data at once?
Sure. Take the result of MATCH as the column number and drag the formula to the right to retrieve the entire row. If there are many headers, it is recommended that you first determine the required fields and drag only to the desired columns to avoid miscellaneous results.
2. Why does VLOOKUP always return one?
VLOOKUP is designed to stop when a match is found, and it does not continue to look down. To list all matches, you must use the FILTER or secondary column method.
3. What if the query column is not at the left end?
You can use XLOOKUP, which can specify any lookup column and return column. If the version does not support it, use the combination of INDEX and MATCH, first use MATCH to find the row number, and then use INDEX to extract the value of the corresponding column.
4. FILTER result appears # SPILL! What is it caused by?
This means that there is something below or to the right of the result area that blocks the spread of the dynamic array outward. Clear these cells, or move the formula to a blank area.
5. The formula is correct, but the data do not match. What may be the reason?
There are three common reasons: the header text is different, causing MATCH to find the wrong column; the search area does not cover the last row; and the numerical value is different from the text format, such as the job number is a number and the text is a text. If you check these three points one by one, you can usually find the problem.
# Summary
VLOOKUP looks for multiple columns by the column ordinal parameter with MATCH, while finding multiple results requires FILTER or auxiliary columns. First confirm the consistency of the Excel version, query column location and header, then select the corresponding writing method, and finally check with the number of rows and sampling comparison, which can make the query results complete and reliable.
