Like a VLOOKUP, but returning multiple values (for multiple matches)

[ben.fish]

Hi,
I'm undertaking a matching excercise, matching employee preferences for new roles to available roles.

I need a formula that effectively generates a shortlist of candidates, finding matches in an array.

I have manually returned the values I would want returned to provide an example (see row 20) , but obviously I need a forumla or macro that c an do this.

If you have any suggestions or advice, it would be much appreciated.

Thanks,

Ben

[Leith Ross]

Hello Ben,

It may just be on my end, but when I open the file nothing happens. No errors, no Excel application, nothing happens. Check that the file is not damaged. It has had 4 downloads and no responses.

[Marcol]

It opened with me, but I had to use the three finger salute to close excel, best check it out.

[Wskip49]

Check out this website.

http://www.excelguide.eu/120/810/120/EN_ARLookUp.html

[martindwilson]

works ok for me try something like attached

[ben.fish]

works ok for me try something like attached

Thanks Martin, it seems to have done the trick. Could you just give a brief explanation of how it works and why you needed to insert a new worksheet?

Many thanks for your help.

Ben

[martindwilson]

i just listed the three preferences on below the other and used an index match to return the choice of each candidate so third choice for example will be in the bottom 10 of the list on sheet2 colb
then i used a count in column c COUNTIF($B$1:B1,B1) give each instance of role1,etc its own number then added column b to it =B1&"-"&COUNTIF($B$1:B1,B1) this value can now be looked for from sheet 1
INDEX(Sheet2!$A$1:$A$30,MATCH(Sheet1!$A20&"-"&COLUMN(A1),Sheet2!$C$1:$C$30,0)))
the match part MATCH(Sheet1!$A20&"-"&COLUMN(A1),Sheet2!$C$1:$C$30,0)
resolves to MATCH("role 1"&"-"&1,Sheet2!$C$1:$C$30,0)
MATCH("role 1-1",Sheet2!$C$1:$C$30,0)
so it finds role 1-1 and returns employe from column a of sheet2 which happens to be employee 1

the rest is just to hide errors when no match is found
this bit
IF(COLUMN(A1)>COUNTIF($D$2:$F$11,$A20)
counts the number of times "role 1" appears in the range $D$2:$F$11 which is 3
so as the formula is dragged across COLUMN(A1) changes to column b1 then c1 then d1...
COLUMN(A1) =1 ,COLUMN(b1)=2 so when it gets to column d1 ,column (d1)=4 ,which is greater than
3 so blank.
as to why its on another sheet ,why not ?,its out of the way and reduces clutter you could put it on sheet 1 if you wanted.