Help Required possible Vlookup

[mdaz]

Hi

Help would be gratefully received.


Im looking for a way to to retrieve data from 1 work sheet to another.

e.g Sheet 1 contains the following data

Name weight cost code
apples 3lb 3.65 aa11
apples 2lb 2.99 ab12
apples 6lb 7 ab11
apples 5lb 6.2 ab13
pears 1lb 1.49 pb1
pears 3lb 4.2 pb2
pears 2lb 2.8 pb3
grapes 5lb 4.49 gb1
grapes 4lb 4.68 gb2

On sheet 2 I type the name 'apples' in say cell A1 which then will return all the information for Apples e,g Weight cost and code.

I have tried using Vlookup but can only seem to get one cell.

again I would be grateful for help.

Thanks in advance.

[63falcondude]

Let's say that the data is in A1:C10 of Sheet1 and that you have Name, Weight, and Cost Code in A1, B1 and C1 of Sheet 2.

With "apples" in A2 of Sheet 2, in B2 of Sheet2, try this:

=IFERROR(INDEX(Sheet1!B$2:B$10,SMALL(IF(Sheet1!$A$2:$A$10=$A$2,ROW(Sheet1!$A$2:$A$10)-(ROW(Sheet1!$A$2)-1)),ROWS($A$1:$A1))),"") Ctrl Shift Enter

Drag this over to C2 and then down until you get blanks.

[mdaz]

Hi


Thanks, that as work perfect.

Much appreciated

[63falcondude]

You're welcome. Happy to help.

If this answered your question, please mark this thread as SOLVED.