Using INDEX(MATCH()) to return the n'th value

问题:

I have a simple worksheet with 2 columns

I want to get all the results(on column "H") (I can get only the first occurrence,I want to know if I can get the others) that contains the value from cell G1, is that possible without a macro ? any way of doing it would be appreciated...Any ideas?

回答1:

You can use ROW() and SMALL() to get those instead of MATCH() since this always gets the first match.

=IFERROR(INDEX($C$4:$C$7,SMALL(IF($D$4:$D$7=$G$1,ROW($D$4:$D$7)-(ROW()-1)),ROWS($D$4:D4))),"Null")

So, if the array $D$4:$D$7=$G$1 returns true (i.e. the value equals that in G1), you will get the row numbers of these values, in this case, you will get 4 and 6. All the other will return False.

After some processing with -(ROW()-1), the 4 and 6 become 1 and 3. those two values are what will be fed to the INDEX.

SMALL() then picks the smallest, starting with the 1st (you get 1 from ROWS($D$4:D4)) and when you drag the formula down, the ROWS become ROWS($D$4:D5) which gives 2, and SMALL ends up taking the 2nd smallest value, which is 3.

EDIT: Forgot to mention. You have to array enter the above formula. To do this, type the combination keys of Ctrl+Shift+Enter after typing the formula (edit the formula again if necessary) instead of Enter alone.