Vlookup for multiple duplicate numerical values

[pete8125]

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.

HELP.

 

[Max]

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,SMALL($C:$C,ROWS($1:1))))
Copy D2 across to G2. Select C22, 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]

Max

Thanks for that, i tried it and it worked perfectly. The only problem is
thay "F101" was an example and i have about 150 fixed call signs (eg F101)
and i couldn't figure this out for multiple callsigns.

I have tried to list the various callsigns in col A but if i listed a second
one it removed the infor of the first.

is there any way of listing the various

 

[Max]

Thanks for that, i tried it and it worked perfectly.

That's good. Kindly press the Yes button in that response, won't you.

I have tried to list the various callsigns in col A
but if i listed a second one it removed the info of the first

Just make a copy* or two of the entire extract sheet (the "another sheet"),
then it enables you to try different values for the input in A2 in the
copies.
*just hold down CTRL & drag the sheet tab to the left/right to make copies

P/s: You can use either advanced filter > uniques, or create a simple pivot
to do a one-time listing of the source col A. For the pivot, just drag n drop
the col header for col A into both the ROW and DATA areas.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---

 

[Ashish Mathur]

Hi,

You may want to try my solution on this page

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com