Multiple results needed

[cherylino]

Probably a daft question, and Im thinking I need index match as vlookup can only look up one result, but I'm hoping somebody can help

I have a list of Job numbers. Sometimes these job numbers are repeated as more than one engineer worked on them, so I wish to look up the job number, and it return me not only the engineer from the first row it appears in, but also the second, third, etc...

Please help!?

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this something that you can work with?
Formula in E2 is copied down.

Excel Workbook
	A	B	C	D	E
1	Job No.	Engineer		Job	Job 2
2	Job 1	Eng 1		Eng	Eng 2
3	Job 2	Eng 2			Eng 4
4	Job 1	Eng 3			Eng 6
5	Job 2	Eng 4			Eng 7
6	Job 3	Eng 5			Eng 8
7	Job 2	Eng 6
8	Job 2	Eng 7
9	Job 2	Eng 8
10	Job 7	Eng 9
Multiple Matches

 

[AlanY]

something like this?

Excel 2013/2016
	A	B	C	D	E
1
2	Philip	2.59%		Philip	2.59%
3	Philip	2.43%		Philip	2.43%
4	Jone	5.00%		Philip	3.00%
5	Rose	3.25%
6	Philip	3.00%
2

Cell Formulas
Range	Formula
E2	{=INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=D2,ROW($A$2:$A$6)-(ROW($A$2)-ROW($A$1))),COUNTIF(D$2:D2,D2)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[cherylino]

Thank you both for responding, I need it to look at the number in one sheet, lets say for example, a1, and the number is there - eg, 12345.

In another sheet the numbers reappear, and next to them are the engineers name, so ineed it to look at it that way, does that make sense?

In laymans (or in my case idiot terms) -

I want to say, look at the number in cell a1 in this sheet, now go to the second sheet and find the same number in the column a (it will appear more than once), and give me the engineers in column b...

Possibly that makes sense?

Thank you

 

[Peter_SSs]

Possibly that makes sense?

Yes it does. It looks identical to what I provided above in post #2 other than that I did it in a single sheet instead of two sheets (because you hadn't mentioned multiple sheets ). Can you make what I gave you work in a single sheet with your data?
If so, it will be easy to translate to two sheets if you can't do it yourself.