View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Vlookup for multiple duplicate numerical values

One easy play which delivers it ..

Source data assumed in Sheet1, cols A to D,
data from row2 down, with key col = col A

In another sheet,
Input for the key col will be done in A2, eg: F101

In C2:
=IF($A$2="","",IF(Sheet1!A2=$A$2,ROW(),""))

In D2:
=IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!A:A,SM ALL($C:$C,ROWS($1:1))))
Copy D2 across to G2. Select C2:D2, copy down to cover the max expected
extent of data in Sheet1, eg down to D200? Mininize/hide col C. Cols D to G
will return the results that you seek, all neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---
"pete8125" wrote:
I'm going to pull my remaining hair out soon about this. Any help will be
gratefully received. Here we go:

I'm trying to run a VLOOKUP to find the manning of a vehicle which will
appear 4 times under the same name and under different times.

A B C D

1 F101 0630-1830 Smith COVERED
2 F101 0630-1830 Jones COVERED
3 F101 1830-0630 Bloggs COVERED
4 F101 1830-0630 Uncovered UNCOVERED


This is a few columns of a huge package od data that i have to filter down
when the information is automatically refreshing and the "F101" bit changes
its position depending upon the day.

I have sorted that out but i have tried to use VLOOKUP to look for just the
information that i want but it will only look at the very first line and i
need it to give me all four. I have managed to change it so that i only have
to look for one time (0630-1830) but i still just get a duplicate result.

What i receive is

A B C D

1 F101 0630-1830 Smith COVERED
2 F101 0630-1830 Smith COVERED
3 F101 0630-1830 Smith COVERED
4 F101 0630-1830 Smith COVERED


The information that i want will always be on the line below the first
result if that helps with this