VLOOKUP

[AP]

I am using VLOOKUP for training participants, the problem which I am facing
now on my list I have got few participants with same surname and my VLOOKUP
picks the 1st participant from the list. Any chance that I can have two or
three cells with Vlookup formula and the cells automatically pick the 2nd and
3rd with same surname.

 

[ryguy7272]

This is a vlookup-type of function which will display multiple results for
your lookup/query:

=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"")
This is committed with Ctrl+Shift+Enter (not just Enter)

Notice:
In this example, the data is in column A and column B, and the criteria that
you are 'looking up' is in cell E1.

As an alternative, you can try these
=IF(ROWS(E$1:E1)<=COUNTIF(A$1:A$20,E$1),SMALL(IF(A$1:A$20=E$1,B$1:B$20),ROWS(E$1:E1)),"")

=IF(ROWS($1:1)>COUNTIF($A$1:$A$20,$E$1),"",INDEX($B$1:$B$20,SMALL(IF(($A$1:$A$20=$E$1),ROW($A$1:$A$20)),ROWS($1:1))))

=IF(ISERR(SMALL(IF(FREQUENCY(IF($A$1:$A$20=E$1,MATCH($B$1:$B$20,$B$1:$B$20,0)),MATCH($B$1:$B$20,$B$1:$B$20,0))>0,ROW(INDIRECT("1:"&ROWS($B$1:$B$20)))),ROWS($1:1))),"",INDEX($B$1:$B$20,SMALL(IF(FREQUENCY(IF($A$1:$A$20=E$1,MATCH($B$1:$B$20,$B$1:$B$20,0)),MATCH($B$1:$B$20,$B$1:$B$20,0))>0,ROW(INDIRECT("1:"&ROWS($A$1:$A$20)))),ROWS($1:1))))
(try to figure this out only if you get bored easily)

Regards,
Ryan---

 

[AP]

Hi
Thanks for trying, but it didn't gave the result which I was looking for. I
will explain in details, sorry I should have done it on 1st querry itself.

two sheets with name platform and participant check

In platform (colomns)
Name, Surname, date of training, venue, etc....

and in the participant check
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 I type the Surname PlatformB:X is the range of Data and 8 is the
'first name' colomn on the platformsheet.

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

Many Thanks