Excel 2007 : How do I get multiple values form a vlookup

[Icehockey44]

Hi Guys,

i'm new here, so apologies if this has been asked before!!!

I am trying to do a vlookup that brings back all data associated with the "look at " data.


Now in the data attached I know that each one has 2 or 3 entries per code. But when I do the vlookup, it will only bring back the first instance. (I.E I know A5PS has two entries of arrived and finished time (14:29:00 and 09:49:00 on the same day)

Is there a way to show each code by day each arrival and finish time?

I've tried this, and it has bought back data, but I cna't work out where from?!?!

INDEX('Raw Report'!$AU$2:$AV$9972,SMALL(IF('Raw Report'!$AU$2:$AU$9972=Sheet2!C5,ROW('Raw Report'!$AU$2:$AU$9972)),ROW(Sheet2!5:5)),2)

as an array formula.

any help would be amazing

cheers

Donna

[royUK]

You could possibly use a PivotTable to get the Count, then use the DrillDown option to get the items for each code

[Icehockey44]

Thanks for that royUK, but Ideally I would need this to be a formula as it is part of an automated work sheet on performance.

I just want to drop in the raw data and everything else updates as per a code that is entered. (If that makes sense!!)

cheers

Donna

[martindwilson]

what are you looking up code.date or what?

[Icehockey44]

what are you looking up code.date or what?

Hi Martin,

What I am looking to get is a list of dates that jobs were done on, who done them (code) and the start and finish times for each job on each day.

The actual data i am working with is about 70,000 lines of data, and the worksheet is designed to tell me by day how many jobs were done in a region by each code. i know need to break that down to individual job and time on and off..I would pop the whole spreadhseet on here but its far too big.

I hope this helps

Donna

[martindwilson]

70000 lines most formulas would probably struggle, what is the final layout going to look like'

[Icehockey44]

Well 70000 might be a bit OTT!!!

I basically want it to look like this:

a1jk <- Enter Code

Calls Attended Arrive Time End Time
03/06/2011 1 12:00:00 12:15:00
10/06/2011 3 9:00:00 9:30:00
10/06/2011 11:00:00 12:00:00
10/06/2011 13:30:00 13:35:00
17/06/2011 3
17/06/2011
17/06/2011
17/06/2011
24/06/2011 1

[Marcol]

Maybe this workbook using helpers to avoid array formulae?

You have multiple entries with differing arrived/finished times, is this correct?
e.g.
01/06/2011 A5PS

This workbook will return the first found times.

[EDIT]
Oops your sample doesn't have calls attended so I missed that bit
Attachment updated.

[Icehockey44]

Wow Marcol, that looks like it will work, I shall send it home and try it out.

I was kind of going in the right direction , but when I joined the Date and code together, it all kind of stopped working!!!

Once agian thanks, and if it does work (It looks like if will) I shall amend as neccesary

cheers

Donna

[Icehockey44]

hi Marcol,

i have looked at this and is whatI am after, but where you have the first code/date unique says 2 calls attended, I need both the start and end times shown.

Are you able to do that?

Cheers

Donna