Show multiple column lookups results in one cell

[declan harte]

Hi,

I am trying to display the results of a multiple lookup in one cell but cannot seem to make it work. I have searched similar questions on this forum and on google but none of the user defined functions or formulaic solutions provided exactly solve my problem.

I have a list of interview candidates in column A with their statuses shown in column B and their interview round # in column C.

In my lookups, I am trying to show, for each round ( 1 thru 4), all candidate names that were successful in one cell, and all candidate names that were not successful in another cell.

Any formula or udf that could achieve this would be very much appreciated. I am using Excel 2010

Name Status Round
AA Success 1
AB Fail 2
AC Success 3
AD Fail 4
AE Success 3
AF Success 4
AG Success 1
AH Fail 2
AI Fail 3
AJ Fail 1
AK Success 2


Round Success Fail
1 AA; AG AJ
2 AK AB; AH
3 AC; AE AI
4 AF AD


Thanks,

Declan

 

[bobhc]

Just turn your data in to a table and filter as you need. It is not recommended to put multi data in to one cell

 

[declan harte]

Thanks bobhc. The lookups will be part of a dashboard summary so I do not want the end user to have to filter the raw data. This is also only a small subset of the data.

 

[p45cal]

A user defined function can do it:

Function blah(names, rng1, crit1, rng2, crit2)
myNames = names
myrng1 = rng1
myrng2 = rng2
mycrit1 = crit1
mycrit2 = crit2
For i = 1 To UBound(myrng1)
  If myrng1(i, 1) = mycrit1 Then
    If myrng2(i, 1) = mycrit2 Then
      blah = blah & "; " & myNames(i, 1)
    End If
  End If
Next i
blah = Mid(blah, 3)
End Function

used as follows:
=blah(the column of names, the column of Status,the Status criterion,the column of Rounds, the Round criterion)

See attached

 

[declan harte]

Thanks p45cal! It worked perfectly.

 

[bosco_yip]

Try this formula with helper column way.

1] Helper Column D2, formula :

="; "&A2&IFERROR(INDEX(D3:D$13,INDEX(MATCH(B2&C2,B3:B$13&C3:C$13,),)),"")

and, select D2 >> Custom Cells Formatting, in the Type box enter : ;;;

All copy down

2] Result Table B16, formula copy across and down :

=MID(INDEX($D$2:$D$12,INDEX(MATCH(B$15&$A16,$B$2:$B$12&$C$2:$C$12,),)),3,99)

Regards
Bosco

 

[bines53]

Hello friends,

Just for fun,
=MCONCAT(INDEX($A$2:$A$12,N(INDEX(ohRemoveInvalidRows(ROW($A$2:$A$12)-1/(($B$2:$B$12="Success")*($C$2:$C$12=A16))),,)),0),",")

MCONCAT---Morefunc add in
ohRemoveInvalidRows---https://sourceforge.net/projects/quantlib/files/QuantLibXL/1.7.0/
QuantLibXL-1.7.0-bin.zip

David

 

[bines53]

Hi Bosco,

I do not understand text functions,
Is it possible to use a formula that I gave,in function MID instead of MCONCAT ?

Regards
David

 

[bosco_yip]

Hi Bosco,

I do not understand text functions,
Is it possible to use a formula that I gave,in function MID instead of MCONCAT ?

Regards
David

Hi David,

1] Purpose of my MID used in post #6 is to remove the leading ";" in the result of which lookup from the helper column.

2] You attached file don't carried with the MCONCAT Add-in programme and the formula unable to work unless downloaded from Morefunc.

3] I think MID cannot replace MCONCAT in your example.

Regards
Bosco