INDEX and MATCH

[dparteka]

I’m using an INDEX MATCH combination formula; VLOOKUP doesn’t work because what I’m trying to return is to the left of the lookup. The formula I’m using works good except I’m looking to also have it continue down the list, skip over the one it just found and then find the next one and continue until it’s found all. I've attached a practice spreadsheet I’ve been playing with, hopefully my explanation makes sense… thanks for looking.

 

[Chihiro]

=INDEX(Sheet2!$A$1:$A$36,SMALL(IF(Sheet2!$K$1:$K$36=C$1,ROW(K$1:K$36)),ROWS(A$1:A1)))

Confirmed as Array (CTRL + SHIFT + ENTER).
Copy down.

 

[dparteka]

Chihiro... that looks like formula wizardry to me. That Array thing is like I just learned a new magic trick. In a single wave of your wand you have solved my problem, thank you, it works perfectly.

 

[Khalid NGO]

Hi to all,

AGGREGATE function can be used with just Enter:

=INDEX(Sheet2!$A$1:$A$36,AGGREGATE(15,6,1/(Sheet2!$K$1:$K$36=C$1)*ROW(A$1:A$36),ROWS(A$1:A1)))

Regards,

 

[dparteka]

Khalid... wow more wizardry and another excellent solution that works perfectly.
Chihiro did teach me about CTRL-SHIFT-ENTER Array function which is new to me and good to know, on the other hand ENTER is simple in comparison. I like it because my human RAM isn't what it once was so in this case less is better.

I will definitely keep both solutions in my arsenal, thanks guys for your great responses... very helpful.