Vlookup help

[superkopite]

Hi Boys and Girls,

I need a little help. I have a set of data with the following columns;

Job Name, Claim Number, Value
Job 1, claim 1, £300
Job 1, claim 2, £450
Job 2, claim 1, £300
Job 3, claim 1, £600

I am trying to use VLOOKUP, somewhat erroneously, to pull across all rows pertaining to each Job. At the moment, with my regular VLOOKUP formula, I am pulling Job 1, Claim 1 across twice, rather than getting both claims 1 and 2.

Any ideas?

Many thanks

 

[alansidman]

to pull across all rows pertaining to each Job.

What does this mean? Are you wanting to copy to another sheet, highlight? Please clarify in simple terms.

 

[steve the fish]

I think i understand. Put your table in A1:C5. In E1 place eg 'Job 1'. Then use:

=IFERROR(INDEX($A$2:$C$5,SMALL(IF($A$2:$A$5=$E$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$1:A1)),COLUMNS($A$1:A1)),"")

Enter CTRL-SHIFT-ENTER or it will not work. Drag across and down until you get blanks.