Vlookup query (aren't they always?)

[ketchel]

Hello folks,

I having a major headache tryign to get my head around this - no doubt there's a relatively simple solution and I'd be greatfeul if someone could point me in the right direction.

I am trying to retreive information from a table for a specific reference. However, there are more than one entries on that table that I want to retrieve, and my problem is I do not knwo how to set up a vlookup function to retrieve the first record found, the second and so on.

e.g.

Name Item bought cost

Flowers dustpan £1.99
Ali bench £7.99
Ali brush £2.99
Quartey pencils £1.89

What I am trying to do is create a lookup function that not only returns the fact that Ali bought a bench, but also that he bought a brush. I can only get vlookup to work so that it gives me the results of the first match it finds (i.e. the £7.99 bench). Is it possible to use the vlookup function to find the next match (i.e. the £2.99 brush)? Should I be combining the lookup function with a match function or countif function?

Thanks

[BigBas]

Check this format, and let me know if it works for you. (Adjust ranges to suit)

[ketchel]

Many thanks for the swiftness of your response.

For some reason I cannot open the file. (Work computer doesn't recognise what program to open it with) Any chance you could paste the function in here?

Thanks.

[BigBas]

I could, but it is a bit more complex than just a formula. The formula is contingent on the setup used. This is why it is important to see the file itself.

It is in .zip format. Since you can't unzip, I will post the formula as well as a .jpg of the setup used.

In cell F3 (as per my setup), enter this formula:

=IF(COUNTIF($A:$A,$G$1)<ROW()-ROW(F$2),"",INDEX(B$2:B$25,SMALL(IF($A$2:$A$25=$G$1,ROW($A$2:$A$25)-ROW($A$1)),ROW()-ROW(F$2))))

NOTE: It is an array formula, it must be enterred with CTRL+SHIFT+ENTER, not just enter.

Next, fill it across to G3, then fill it down.

Let me know if that works.

[ketchel]

It does work - thanks very much....

Just need to try and decipher what it is saying so I can have a play with it and apply it to a few more tables. Will try and post here again.

Cheers

[Teethless mama]

It does work - thanks very much....

Just need to try and decipher what it is saying so I can have a play with it and apply it to a few more tables. Will try and post here again.

Cheers

The easiest way is using the AutoFilter