VLOOKUP please help

[AP]

I have got two sheets with names platform and participant check

In platform (colomns), I had specified the details for the training
participants like
Name, Surname, date of training, venue, etc.... It could be above 700
participants

and in the participant check the 2nd sheet
when I type the Surname of participants it picks from the platform all the
details for the purticular participant.
the formula which I used is =VLOOKUP(B3,Platform!B:X,8,FALSE)
where as B3 is the Surname (which I have to type) PlatformB:X is the data
area and 8 is the 'first name' colomn on the platformsheet.

In the participant check I was hoping it to give me an option where once I
type the surname it shows me all first name if any with same surname. or on
another cell showing that there is another person with the same surname

Please help

 

[Teethless mama]

=IF(ISERR(SMALL(IF(Surname=$B$3,ROW(INDIRECT("1:"&ROWS(Surname)))),ROWS($1:1))),"",INDEX(First_name,SMALL(IF(Surname=$B$3,ROW(INDIRECT("1:"&ROWS(Surname)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed

 

[AP]

Hi,
Thanks for helping, but it didn't work, as I mention that the formula should
search the data in another sheet which is platform!. Because thats the sheet
contain all the information and sheet2 is just to check the participants