Excel 2007 : lookup retunring multiple values

**Icehockey44** · 11-24-2011, 06:46 AM

hi Guys,

I have an example of a list of job that was carried out by engineers, I need to record in one line which companies they have visited on that day.

i have doen one previously that was simular, but I can't ge this one to work!!

It is supposed to be an array but when I put the array on all the data disappears!, and the data that stays is right but in the wrong order!!

IE 03/11/11 the enigneer attended 2 jobs, one of which was 'down' at BIG, but my formula brings back that the job was at DOG. But in the formula I am trying to vookup from the unique (eng/date/down) cell (in this instance B5) so the DOG is on column 5 on the raw data sheet. this is exactly the same as my other one but seems to have gone wrong somewhere!!

can anyone please look at my formula and see where I hvae gone astray!!

cheers

Donna

**Richard Buttrey** · 11-24-2011, 07:12 AM

Hi Donna,

There area couple of things wrong.

First your raw data only contains the 2 digit year reference in column X, yet on the Worksheet column B you are using a 4 digit year. You'll therefore never find a match. Change B5 to

Please Login or Register  to view this content.

and copy down.

Then in I5 and copied down you can use

Please Login or Register  to view this content.

Note since you're using Excel 2007 you can use the =IFERROR() function instead of the wasteful doubling up test that we needed to use in Excel 2003.

Regards

**Icehockey44** · 11-24-2011, 07:32 AM

Thakyou very much Richard,
fresh eyes found these simple errors!!! worked a treat,

I now need to find the second visit of the day and so on. I have anchored the Y's and X's along with B5 (my lookup cell) and changed the 1 to a 2 (on the guess that i am telling it to bring back the 2nd instance), but it doesn't seem to work.

do you have another suggestion for this?

thanks for your help so far, it has helped a lot.

cheers

Donna

**Richard Buttrey** · 11-24-2011, 09:11 AM

Hi Donna,

No, the '1' is nothing to do with the second instance. It's just indicating which column to use in the range defined by the INDEX() function.

For the second instance you need to use a modified formula in J5, since you want the Index to start at the row after the row which contains the first instance.
So in J5 and copied down...

Please Login or Register  to view this content.

One further thought. If you're wanting the 3rd, 4th etc visits you may be better advised to use a Pivot Table which will give you much more flexibility. Or alternatively consider a Data Filter Advanced.

Regards

**Icehockey44** · 11-24-2011, 11:43 AM

thanks for this richard, I have experimented with pivot tables, but they don't "look" as good a self designed ones!!.

I have managed to amend the original one that I created, not sure why when recreating it, it didn't work! but thats computers for you!!

I have 101 and things that this formula will help out on.

Once agian many thanks for your helpand patience.

Donna

**Icehockey44** · 07-27-2012, 05:11 AM

Hi All,

sorry to repoen one of my old posts, but I need to expand on this formula for another project.

Basically I need to now look for more than 2 instances, I have had a go and works to a point then it all goes wrong.

I have attached an example of how I thought it would work, but clearly it doesn't so have added columns to say what it should have brought back.

i look forward to your help

Donna