CODEWITHSUNDEEP
excelvbaexcel-formulamatchuser-defined-functions
User123456789
User123456789

Reputation: 149

MATCH and INDEX to return index number 2 Excel

I tried with the combination of CONCATENATE, INDEX and MATCH function, to get an overview table of which employees works which function a day.

Here is the setup:

enter image description here

And this should be the resulting table:

enter image description here

This is the formula I made:

=CONCATENATE(INDEX($B$5:$B$10,MATCH($B2,C$5:C$10,0)))

And obtain only the first index:

enter image description here

How can a return index 2 respectively: name3 and name5?

I have an Excel version prior to the 2016 version, and can't use the TEXTJOIN or CONCAT function.

EDIT

I tried to make the formula work even though the VBA solution works as well:

This is what I get to work:

=IF(COUNTIF(C$5:C$10,$B2)=0,"",IF(COUNTIF(C$5:C$10,$B2)=1;INDEX($B$5:$B$10,MATCH($B2,C$5:C$10;0)),INDEX($B$5:$B$10,MATCH($B2,C$5:C$10,0))&CHAR(10)&INDEX($B$5:$B$10,SMALL(MATCH($B2,C$5:C$10),ROWS(C$5)))))

It gives the correct solution in this case (assuming there are at most 2 matches), however it's fragile.

I tested it in a larger setup, where it returned the same name twice, and I can't figure out what the problem is.

Any idea, why that's the case?

Upvotes: 0

Views: 62

Answers (1)

Andreas
Andreas

Reputation: 23958

Nothing pretty at all. But it does work.

User defined function.
Place this code in a module

Function name_find(func As String, namerng As Range, searchrng As Range)
    
    For Each r In searchrng
        If r.Value = func Then
            name_find = name_find & Chr(10) & Cells(r.Row, namerng.Column).Value
        End If
    Next r

End Function

and use the formula:

=name_find(C4,C9:C14,D9:D14)

enter image description here

Remember to enable line breaks in the cell. It took me a while to understand why it didn't work :-)

Upvotes: 1

Related Questions