Single Criteria Multiple Value return

[mrzoogle]

Hi All,


Hope you all doing great. I've got a question which has been bothering me for days now.


This can be done using either pivot table or VBA but what I want to find out is how to drive the same results using formulae.


Assume that we have value as below in Sheet1:


Name Cost Code

Smith 1453 A

John 1263 B

Smith 2123 C

Smith 2341 D

Tom 1654 E

John 1655 C

John 1656 D

John 8965 E

John 22235 B

Tom 66432 C

Smith 2341 D

Tom 132 E

Tom 8956 D

John 123 E

Tom 3452 E

Smith 22784 D


In sheet2 I would like to type in cell A1 the name and from B3:C17 results will be populated via formulae.


The results will look like as below:


Type name

Smith

Cost Code

1453 A

2123 C

2341 D

2341 D

22784 D

Tried Vlookup, sumproduct & index matching but none of the seem to be able to do the job. Or it is very possible that I am missing something very obvious ?


Thanks for your time and looking forward to learn something new.


Regards,

Z.

 

[mrzoogle]

Ah let me try this link provided by:


NARAYANK991


http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/


This might be what I need to look for.


Thanks,

Z.

 

[oldchippy]

Hi mrzoogle,


Here's another you may find of interest


http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx