Vlookup for a cell with multiple criteria

[AdrianCepa]

I have a spreadsheet which looks like this
[TABLE="width: 407"]
<tbody>[TR]
[TD]in the second spreadsheet (Sheet2) i have a list of names with associated references to them. what id like is these refferences to appear in collumn P seperated by a comma like the names are in collumn O, is this possible?
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]O
[/TD]
[TD="align: center"]P
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]General Access
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]General Access
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]
[TABLE="width: 407"]
<tbody>[TR]
[TD]Registry Administration, Kumar Ramu, Ubaidillah A Hasan, Amran Jamaluddin, Norizah Hamzah, Amelia Rosdiana Othman, Anne Ong, Salwiyah Jumaah, Noorashikin A Mohd Ali, Nor Hafiza Abizaid
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]General Access
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]
[TABLE="width: 407"]
<tbody>[TR]
[TD]Registry - Administration, Daryl K Wood, Kumar Ramu, Anne Ong
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AdrianCepa]

I tried to copy and paste but the borders dont come through

 

[AdrianCepa]

https://onedrive.live.com/#cid=07EA8134155CADB3&id=7EA8134155CADB3%21105

This is my one drive where i have attached a dummy file, can you access it?

 

[Aladin Akyurek]

https://onedrive.live.com/#cid=07EA8134155CADB3&id=7EA8134155CADB3%21105

This is my one drive where i have attached a dummy file, can you access it?

That appears to be empty.

Try the following recipe...

Copy a tiny representative sample from your workbook.
Paste it here.
Click in a cell of what you just posted.
Click on the Go Advanced button.
Click on the icon for Table Properties.
Choose the Full Grid option for Table Style.
Click OK.

 

[AdrianCepa]

This will hopefully work,

this is a table from "sheet1" in my work book, it has some restriction groups and some names, The table Underneath is from sheet 2 and shows the Refference for these people, is it possible for a formula in excel to pick out the names in cells from table in Sheet 1 and lookup the refferences for these people in the table from sheet 2? (all the data is in the first two collumns of both the spreadsheets)

if this table doesnt make sense, i have inserted another file into the one drive which should work now, otherwise i have no clue how to show you sorry

[TABLE="class: grid, width: 305"]
<tbody>[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Registry Administration, John Smith, Alan White, Graham Willis, Andy Grey[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]General Access[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Registry Administration, John Smith, Alan White, Graham Willis, Andy Grey[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Registry Administration, John Smith, Alan White, Graham Willis, Andy Grey[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Registry Administration, John Smith, Alan White, Graham Willis, Andy Grey[/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]
[TABLE="class: grid, width: 252"]
<tbody>[TR]
[TD]John Smith[/TD]
[TD]H12345678[/TD]
[/TR]
[TR]
[TD]Alan White[/TD]
[TD]H12345677[/TD]
[/TR]
[TR]
[TD]Graham Willis[/TD]
[TD]H12345666[/TD]
[/TR]
[TR]
[TD]Andy Grey[/TD]
[TD]H12345555[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[AdrianCepa]

https://onedrive.live.com/#cid=07ea8134155cadb3

This is the URL which should work now as well

 

[Aladin Akyurek]

Great. What would be the actual result that you want to see in B11 of the first exhibit?

 

[AdrianCepa]

"H12345678, H12345677, H12345666, H12345555" if thats possible

 

[Aladin Akyurek]

"H12345678, H12345677, H12345666, H12345555" if thats possible

B1, control+shift+enter, not just enter, and copy down:

=IF(LEFT($A1,LEN("Registry Administration"))="Registry Administration",
  REPLACE(aconcat(IF(ISNUMBER(SEARCH(","&SUBSTITUTE(Sheet2!$B$1:$B$4," ","")&",",
  ","&SUBSTITUTE(A1," ","")&",")),", "&Sheet2!$C$1:$C$4,"")),1,2,""),"")

ACONCAT is a function coded in <ACRONYM title="visual basic for applications">VBA</ACRONYM>, which is given below. You need to add this to your workbook using Alt+F11 in order to run the foregoing formula.

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

See the workbook that embodies the foregoing set up:
https://dl.dropboxusercontent.com/u/65698317/AdrianCepa%20test.xlsm

 

[AdrianCepa]

Thank you for this it works amazingly!! now ill have to try and put it into my spreadsheet thank you once again!

 

[Aladin Akyurek]

Thank you for this it works amazingly!! now ill have to try and put it into my spreadsheet thank you once again!

You are welcome. Thanks for providing feedback.