VLOOKUP to return multiple results in one line

Hi

I need a VLOOKUP function that will compare e-mail addresses on sheet "main sheet", column A with E-mail addresses on sheet " Data sheet" column D and return the corresponding data "Data Sheet" column A on sheet "main sheet" column B.

I used this formula on "main sheet", =VLOOKUP(A2;'Data Sheet'!$A:$E;2;TRUE) - see attached example

but I need the result to have all possible results.

Thus multiple results in one line. Meaning if it was critical for peter in row 1 and Medium in row 3, then both to show in "main sheet" column b

Thanks for the assistance

Here's a way of doing it if you are using XL2007 (like me).

Put this formula in E2 of the Data Sheet:

=IF(D2="","-",D2&"_"&COUNTIF(D$2:D2,D2))

Copy this down to the bottom of your data (or beyond, if you expect to add more in the future - the hyphens will show where the formula is active).

Then you can use this formula in B2 of the Main sheet:

=IFERROR(INDEX('Data Sheet'!$B:$B,MATCH($A2&"_1",'Data Sheet'!$E:$E,0))&" ","") & IFERROR(INDEX('Data Sheet'!$B:$B,MATCH($A2&"_2",'Data Sheet'!$E:$E,0))&" ","") & IFERROR(INDEX('Data Sheet'!$B:$B,MATCH($A2&"_3",'Data Sheet'!$E:$E,0))&" ","")

this will give up to 3 multiple results per email, but it can be easily expanded for more, if required.

Hope this helps.

Pete

I use Office 365 will it be the same?

thanks for the help so far