The match function returns multiple values

excel table inside the match function appears more than one match how to return more than one reference cell content?

E2 input


Array of formulas, first hold down the CTRL + SHIFT, and finally enter, so that the edit field Brackets {} appear at the ends of the formula

Formula dropdown

MATCH returns two matches with the same value?

If you are using the MATCH function to find data that has the same value, and multiple matches return the same line number, then the MATCH function will only return the position of the first match. This may cause you to fail to find the location of all matches.

One way to solve this problem is to use other functions that work with the MATCH function. For example, you can use the SMALL function to find the Nth smallest value that is less than or equal to a given value. If you set N to 1, then you will get the first minimum value, and if N is set to 2, then you will get the second minimum value. Here is an example formula:


In this example formula we want to find all the rows in column A that have a value equal to 5. We use the IF function to create a logical array that returns TRUE if the cells in A1:A10 are equal to 5, and FALSE otherwise. we then add ROW(A1:A10)-ROW(A1)+1 to the cells with the TRUE value, which returns the row number of each matching item. Finally, we use the SMALL function to find the first minimum value.

To find the second minimum, simply change “1” to “2” in the formula.

Similarly, you can use the LARGE function to find the Nth largest value greater than or equal to a given value. To find the same value, you can compare the row number in the matching cell with the values in other columns to find cells with the same value.

Hope this helps you solve your problem.

How to return multiple reference positions when multiple matches occur in the match function inside an excel sheet?

No need to match function. Change to other formulas on the line: x0a such as column A has more than one “A”: x0a = IF (ROW () & gt; COUNTIF (A: A, “A”), “”, SMALL (IF (A $ 1: A $ 100 = “A”, ROW ($ 1: $ 100), 4 ^ 8), ROW (1: 1))) x0a array formula, enter the first line and then return multiple references. )))x0a array formula, enter the first do not enter, press Ctrl + Shift + Enter to end the calculation, and then fill down. x0a that is to return to match the value of “A” in the line number.

EXCELINDEX+MATCH how to return multiple results that satisfy the condition?

=INDEX(index column, SMALL(IF((region1=condition1)*(region2=condition2),row(region1),65536),ROW(A1)))&””

Array formulas, at the same time, press the CTRLSHIFT Enter key

No row and column numbers, their own variations

< p>It is possible to ROW (A1)

To be replaced by SUMPRODUCT in order to find out the number of conditions met

excel two cell conditions to return a variety of values, how to write the function

The need to use the IF function, the function formula is as follows:

Formula: = IF (A10=””,””, LOOKUP(A10,{0,1;20,2;30,3;40,4}))

Or: = IF (A10=””,””, MATCH(A10,{0;20;30;40}))

LOOKUP function:

Returns values in a vector or array. Function LOOKUP has two syntactic forms: vector and array. Function LOOKUP vector form is in a single line region or a single region (vector) to find the value, and then return to the second single line region or a single region in the same position in the value; function LOOKUP array form in the first line of the array or the first column to find the specified value, and then return to the last line of the array or the last column of the same position in the value.

MATCH function:

MATCH function, refers to the return of the specified value in the specified array region. MATCH function is one of the main EXCEL lookup function, the function usually has the following aspects of the use:

(1) to determine the list of the location of a value;

(2) a test of a certain value of the input to determine whether this value exists in a particular list;

(3) determining whether duplicate data exists in a particular list;

(4) locating the position of the last non-empty cell in a particular list.

Looking for text values, the function MATCH does not distinguish between upper and lower case letters.