From a fan's contribution: The meal details of employees in a factory are in Table 1, and now the employee's work number needs to be entered in Table 2, so that the employee meal schedule with the specified employee number entered in Table 2 can be automatically brought out in Table 3.
For the convenience of screenshots, the three tables were screenshotted into a working page, and the effect is shown in Figure 1 below
Requirements analysis
When I first saw this requirement, I thought it was a search and reference requirement, and I could query it directly with xlookup. But after careful breakdown, it turns out that it is not so simple, because the data is on three tables, and the function of each table is different.
Table 1 is the basic data, which is the details of employees' meals, and Table 2 is the input conditions, that is, only the employee number is entered here. Table 3 is automatically generated, and the format requirements are exactly the same as Table 1. If you use xlookup directly, the work number will be missing.
Therefore, you need to change your thinking, you can use the new function chooserow in this update, and the logic is also simple, first use match to determine the number of rows (positions) of these job numbers in Table 1, and you can return the corresponding rows through these rows.
The logical tree is shown in Figure 2 below
Determine the line number
The first step is to know that the entered employee number corresponds to the line number in Table 1, and the function match to determine the location can solve this problem
match(h4:h7,b4:b13,0)
Function definition: Determine h4:h7 (work number), and in Table 1, the position of b4:b13, parameter 0 represents an absolute match. The results are in the first row;
The effect is shown in Figure 3 below
Select Row Data
Through the match function, you can know the relative position of the entered job number, and you can cooperate with the chooserows function to return the data of the specified area. Entry Function:
j4=chooserows(b4:f13,match(h4:h7,b4:b13,0))
Function Definition: B4:F13: This is a data range that contains the rows to be selected. This is the result that needs to be displayed (Table 3).
match(h4:h7, b4:b13, 0): This part is the position returned by entering the work number in Table 2 above
chooserows function: It selects a specified row based on a given index value. In this formula, it will select the corresponding row based on the position returned by the match function.
Taken together, the function of this formula is to find a match in the lookup range b4:b13 based on the conditional range h4:h7 and return the row index where the match is located. Then, use the chooserows function to select and extract the corresponding rows in the data range B4:F13.
After completion, it is shown in Figure 4 below
Knowledge Points:
Find Position Function: match, which returns the corresponding position of the element in the array that matches the specified item in the specified way, parameter: match(Find value, Find area, [match type];
Returns the specified row function: chooserows, which returns rows in an array or referenceParameter: chooserows(array, the number of rows is 1......）
It can be said that chooserows and choosecols can easily intercept and organize various data with a selection row and a selection column, with the match function.
252 WPS New Function Example: Employee name and employee ID are quickly separated.
251 WPS New Function Case: Quickly sort and count customers.
250 wps New Function Example: Quickly classify and count employees' birthdays.
249 wps new function example: multi-condition statistics of orders.