Tips for learning Excel functions: Use the Column function to make formulas more flexible!

Many times, the difference between multiple formulas is just the column parameters. It would be too clumsy to manually modify the column parameters after copying or filling in the formula. You can use the Column function to make column parameters, making the formula more flexible and easier to use.

When I first learned VLOOKUP, whenever I encountered multiple columns of data, my method of operation was to manually change the third parameter in the formula one by one. For example, below I need to find the student's gender and scores in each subject. My past operations are as follows.

Are there any classmates who operate as stupidly as me? Please raise your hand

#If there are many matching columns, manual modification like mine would be not only error-prone, but also particularly inefficient. So what's the solution?

Yes, use the COLUMN function to replace the column parameters in the formula.

1. COLUMN function

Let’s briefly talk about the meaning and usage of the COLUMN function.

The COLUMN function is used to obtain the column number, using the format COLUMN(reference), where Reference is the cell or cell range whose column label needs to be obtained. There are three typical uses.

1.COLUMN()

The parameter is empty, COLUMN() returns the column coordinate value of the cell where the formula is located, the following formula is located in cell B7, so the return value is 2.

2. COLUMN(C4)

The parameter is a specific cell, such as COLUMN(C4), return C4 Column number 3 is as follows.

3. COLUMN(A2:E6)

The parameter is a cell range, such as COLUMN(A2:E6), return The column number value of column 1 in the area (the column where A2 is located) is 1, as follows.

2. Use COLUMN to replace the third parameter of VLOOKUP

Now go back to the previous page to find the student’s gender and In the case of scores for each subject, VLOOKUP and COLUMN are nested. The formula in cell K2 is changed from "=VLOOKUP($J:$J,$A:$H,2,FALSE)" to "=VLOOKUP($J:$J,$A:$H, COLUMN(B2), FALSE)", and then right-click this formula to directly match the other 6 values, without having to manually modify the third parameter one by one. When you pull the formula to the right, you will find that the third parameter automatically changes to COLUMN(C2), COLUMN(D2), COLUMN(E2), COLUMN(F2), COLUMN(G2), COLUMN(H2). Please see the demonstration effect↓↓↓

Is the efficiency much higher and less prone to errors? Especially useful when the amount of data is large.

3. VLOOKUP COLUMN quickly fills in salary slips

The nesting of Vlookup and COLUMN functions can also be used to create salary slips, and The greater the number of employees, the easier it is to use this method. The following table is the salary table of some employees of a company. Now we want to make it into a salary slip. How to complete it?

(1) You can copy the table list title in the H1:M1 area.

(2) 9 employees, each with 3 lines of salary slip, a total of 27 lines are required. Select G1:G27, enter any input number and press Ctrl Enter key to fill. This column is prepared for double-clicking to fill down, so as to avoid the inconvenience of dragging down to fill when there are many employees.

(3) Enter serial number 1 in cell H2, and then enter the formula in cell I2:

=VLOOKUP($H2,$A$2:$F$10, COLUMN(B2),)

(4) Pull right to fill in the formula.

(5) Select the H1:M3 area, double-click the fill handle (small square) in the lower right corner and fill it down to complete the production of the salary slip.

Please see ↓↓↓ for operation demonstration

In addition, the nested use of Vlookup and COLUMN functions can also be used to adjust the sorting of table contents. ,

4. Vlookup COLUMN nesting adjusts the data order according to the template

There are two monthly product sales tables. The order of the products in Table 1 is correct and it is a template. The product order in Table 2 has been disrupted. Now it is required to restore Table 2 to the template order. How to achieve this?

The method that many people think of is to copy and paste the products in Table 1 into a certain area, and then use the Vlookup function formula to search and match the values ​​​​in Table 2. However, you can actually use Vlookup and COLUMN functions to nest formulas in one step, eliminating the need to copy and paste. Please see the demonstration effect↓↓↓

Related learning recommendations: excel tutorial

The above is the detailed content of Tips for learning Excel functions: Use the Column function to make formulas more flexible!. For more information, please follow other related articles on the PHP Chinese website!