Return Multiple Values

[amit_gupta123]

Hii


i have two Lists with me List 1 & List 2. In List 1 some numbers are there and list 2 persons are there and some persons are repeated in nature. List1 in Col A & List 2 in Col B. i am trying to get result that a person gets all the nos matching to his name and after all search pertaining to his name fininshes then from List 2 aanother name displays and the numbers start reflecting against that name. For comfort it should look like as under:i want to do it using formulas bot a macro

Col A Col B Col D

List1 List 2 result

31 Michael Michael 31

71 John 77

99 Jack 20

44 Michelle John 71

55 Maggie 88

77 Michael Jack 99

88 John Michelle 44

66 Maggie 60

60 Michelle Maggie 55

20 Michael 66

 

[NARAYANK991]

Hi Amit ,


I think the easiest way to get the result that you want is to do the following :


1. Sort of data range of columns A and B , by column B ; this brings together all the persons with the same names.


2. In the third column , introduce the following formula :


=MATCH(Person_Name,List_of_Names,0)


This will ensure that the ordering of the names is the same as your original list.


3. Sort all three columns ( Numbers , Names , Original order position ) on column C.


Narayan