The VLOOKUP function returns a single column (cell) as the search result.
If you want to return more than one column, you will have to create VLOOKUP functions for multiple cells, changing the column number (argument 3).
If possible, we would like to cell-copy the formula, but if we Copy and Paste normally, the column numbers will be copied as they are in the first formula without counting up.
While it is possible to manually rewrite all the column numbers, the large number of target columns is both time-consuming and burdensome, and the possibility of making a mistake is high.
Here are some formulas that can be simply Copy and Paste.
Steps.
There are four methods.
- Use the XLOOKUP function instead of the VLOOKUP function.
- Enter the column number in the cell.
- Use the COLUMN function together.
- Use the INDEX and MATCH functions instead of the VLOOKUP function.
The first, XLOOKUP function, solves many of the problems of the VLOOKUP function.
It is most recommended for any version that can use the XLOOKUP function.
The second method is recommended because it is simpler.
The third method is used when you want to complete the formula in the VLOOKUP function cell only.
The fourth method is to combine the INDEX and MATCH functions instead of the VLOOKUP function.
This is smarter than the VLOOKUP function, although the formulas are slightly more difficult to read.
If you do not mind the disadvantage of a less well-known function, this is the way to go.
The XLOOKUP function is superior, but the INDEX and MATCH functions functions have the advantage that they can be used with older versions.
Use the XLOOKUP function instead of the VLOOKUP function.
In the XLOOKUP function, what used to be a number specification for the display column becomes a range specification and responds to Copy and Paste and auto-fill.
In the following example, the following is set in cell J4 and then copied and pasted into cells K through L to complete the process.
=XLOOKUP($I4,$C4:$C9,D4:D9)
Spill in the column direction are also efficient.
To do so, set the following in cell J4.
=XLOOKUP(I4,C4:C9,D4:F9)Entering this formula will automatically expand the formula to K-L without Copy and Paste.
Enter the column number in the cell.
Write column numbers on any line. (Row 1 in the sample below)
Sequential numbers are efficient because they can be entered using auto-fill.
Even if it is not consecutive, it is very easy and error-free compared to editing formulas in functions.
=VLOOKUP($I$4,$C$4:$F$9,J1,FALSE)
Use the COLUMN function together.
Change the column number specification to the formula below.
=COLUMN() - fixed number of columns to adjust
Use the INDEX and MATCH functions instead of the VLOOKUP function.
Combining the INDEX and MATCH functions results in a cell range specification instead of a column number. (Same situation as the XLOOKUP function)
Use the formula below instead of the VLOOKUP function.
=INDEX(D4:D9,MATCH($I$4,$C$4:$C$9,0))
---