A One-to-many query method for data in Excel tables

a One-to-many Query method for data in Excel tables

For example, as shown in the following figure, the left a1:c10 is a list of trainees, and it is now necessary to extract all the student lists of the class in the F2:f10 range based on the conditions specified in the "Eh class" of F1 cell.

Today said a function query aspect method: Index+small.

F2 cells Enter the following array formula, hold down the Ctrl+shift key, press ENTER, and then fill down:

=index (B:b,small (IF a$1:a$10=f$1,row ($1:$10), 4^8), ROW (A1)), "")

Formula explanation

IF (A$1:a$10=f$1,row ($1:$10), 4^8)

This part, first determine whether the value of a1:a10 is equal to F1, if equal, then return a column of the corresponding line number, otherwise return 4^8, that is 65536, in general, the worksheet to this location will have no data.

The result is an array of memory:

{65536;2;3;65536;65536;65536;65536;8;65536;10}

The small function takes a number of the results of the IF function and, with the downward padding of the formula, extracts the 1th, 2, 3......N minimum, which in turn obtains the line number that meets the class conditions.

Then, using the index function, the row number returned by the small function as the index value, the corresponding name result is extracted in column B.

When the result of the small function is 65536, means that the qualifying line number has been exhausted, and the index function then returns the reference to B65536 cell, resulting in a meaningless 0, in order to avoid this problem, you can add a small tail to the formula followed by & ""

Using the & "" method, it is very clever to avoid the emergence of meaningless 0 value, only when the search results are numeric or date, this method will convert the value into a text value, is not conducive to the accurate presentation of data and again statistical analysis.

Practicing problem

Finally left a practicing question, the following figure, according to the A1:C10 area data, the E column related class name, fills the F2:i5 area.