Example of using array functions in Excel

There are two worksheets in the workbook Sheet1 and Sheet2, first say SHEEI1, as follows

A B C

Serial number name of the item

101 1 Car Straps

101 2 combined Front piece

101 3 after the film

101 4 Rider Belt

101 5 Car Side film

101 6 Close Front bag

101 7 car with back belt

202 1 Front sheet chain

202 2 Front Bag

202 3 Pack

202 4 car Rear piece

202 5 Rider Pad

202 6 Close Front bag

Table Sheet2 as follows:

A B C

Serial number name of the item

101 5 [This cell blank]

101 2 [This cell blank]

101 3 [This cell blank]

101 7 [This cell blank]

202 3 [This cell blank]

202 1 [This cell blank]

Requirements according to Sheet2 content, find Sheet1, the Name column in Sheet2 fill in the correct content.

Topic Analysis:

According to the requirements, if you want to fill in the correct name for a row of Sheet2, you first need to find the line in Sheet1: The first column of the row is equal to Sheet2 a row n the first column, the second column of the row is equal to Sheet2 a row n the second column.

Because it involves the search of data, it is to be implemented by array formula.

1. First define the name:

(Define name use menu: Insert-Name)

Dataa=offset (sheet1! $A $1,0,0,counta (sheet1! $A: $A), 1)

Datab=offset (sheet1! $B $1,0,0,counta (sheet1! $B: $B), 1)

The advantage of this is that no matter how many rows Sheet1 adds, Dataa always includes rows with content in column A, which saves a lot of computational time compared to using a:a entire columns directly. Datab the same.

2. Matching conditions

Match a column first, select sheet2! D2:D13 (because the original data is from the 2~13 line), enter in the formula bar

=if (dataa=a2,1,0)

Use ctrl+shift+ carriage return, this can see the general and sheet2! A2 matched Sheet1 rows, where they all turned 1, and the mismatch was 0.

Similarly, in sheet2! E2:e13 input

=if (datab=b2,1,0)

So we get the difference between the D and E columns and the sheet2!. A and sheet2! Array of column B matches.

3. Merge Matching Criteria

We need two conditions with the operation, so using multiplication is the best, only two conditions are set up at the same time, 1*1=1, otherwise two multiplier inside at least 1 are 0, the result is 0.

At sheet2!. Input array formula in F2:F13:

=if ((DATAA=A2) * (DATAB=B2), 1,0)

In this way, we get a new array of 1 and 0, with an element corresponding to the Sheet1 row if it matches, the element is 1, or 0.

4. Retrieve line number

With this array, we need to get the matching line number, because the array is made up of 0 and a 1, so we just need to use the match function to get the sheet2! An A matching Sheet1 line number.

In sheet2! G1 input

=match (1,if (DATAA=A2) * (DATAB=B2), 1,0), 1)

Note that although this is just a single cell input formula, but still an array formula, you need to use Ctrl+shift+enter.

The result is the matching line number in the Sheet1.

5. Search results

With the line number, the search results are easy, and we use the indirect function.

In sheet2! C2 Input Array formula:

=indirect ("sheet1! $C $" & MATCH (1,if (DATAA=A2) * (DATAB=B2), 1,0), 1)

Pay attention to still use ctrl+shift+enter, the correct result appears.

For the following line, just drag the fill handle to copy the formula.

Also, don't forget to delete the formulas that are no longer needed in the D, E, F, and G columns that are used to demonstrate.

Classification:

• Excel Tutorials