Vlookup with duplicate unique items

[John]

I have a one master file with a worksheet like below that I send out to
multiple people,it's about 2k rows long:

A B C D
1 40921 John Booked Low risk

2 40922 John Booked

3 40923 John

4 40924 Peter

5 40925 Peter

6 40925 Peter

What I tried doing is copying all the worksheets that I get into one and
using a Vlookup to pull data from columns C and D into one sheet, but with
vlookup it pulls the first piece of data it finds so sometimes I end up with
a blank when I know data is present. Does anyone know of a formula that can
fit my needs?

JS

 

[Sheeloo]

If your unique combination is 40921 AND JOHN then combine them in one COL by
=A1&B1
If you do that after inserting a Col after COL B (in Sheet2) then use this
for lookup

=VLOOKUP(A1&B1, Sheet2!C:E,3,FALSE)

 

[John]

I tried that, but when I consolidate all of these worksheets into one, i have
more than one piece of unique data, so for example:

40921 John
40921 John
40921 John Booked Low Risk
40924 Peter
40924 Peter Low Risk
40924 Peter
40925 Lillian
40925 Lillian Booked
40925

So when I run the vlookup it returns nothing because it picks the first one
it finds.

 

[Sheeloo]

Sort DESCENDING on Col C & D so filled rows come first, then your lookup will
find them