vlookup returns more than one value??

[MAANI]

A B(Date) C D(Badge #) E F G(wo #)
26-Sep-09 733 1189
27-Sep-09 902 1189
27-Sep-09 902 1151
27-Sep-09 733 1148
27-Sep-09 733 1189
28-Sep-09 852 1189

I need to know which badge #s worked on wo # 1189 on the 27th-Sep ! In this
case 902 and 733. Note that I'm using excel 2007 and my sheet has 800 000
rows.

 

[Eduardo]

Hi,
do a pivot table and the sort by date, or badge #

 

[Teethless mama]

Try Advanced Filter

 

[Max]

One easy, dynamic formulas tinker ..

With your source data as posted in row2 down
Enquiry/input variables
In I2, eg: 27-Sep-09
In I3, eg: 1189

In J2: =IF(AND(B2=$I$2,G2=$I$3),ROW(),"")
Copy J2 down to cover the max expected extent of source data

Then in K2:
=IF(ROWS($1:1)>COUNT(J:J),"",INDEX(D,SMALL(J:J,ROWS($1:1))))
Copy K2 down by the smallest extent large enough to cover the max expected
number of results per enquiry, say down to K20? Col K will return all the
multiple results (if any) from col D (Badge#) satisfying the input variables
in I2:I3, neatly bunched at the top

Above concept should work fine, albeit recalc performance-wise untested here
on 800k source rows (I don't have xl07). The recalc-intensive formula is col
K's, which fill extent (as advised in the construct steps above) should be
kept minimal to enhance performance. You could also manage recalc performance
via setting it to manual calc mode, then press F9 to recalc only whenever
required.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik