Can I loop Vlookup formula ?

[Jwaied]

I am making an LPO Tracker for flight tickets, with the vlookup i can take one name for each LPO number.

My problem is that i need to put multiple names with the same LPO number, unfortunately the vlookup takes only the first name and ignore the rest.

here is an example,
[TABLE="width: 200"]
<tbody>[TR]
[TD]Lpo Number[/TD]
[TD]Candidate Name[/TD]
[TD]Flight date
[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Mary[/TD]
[TD]12.03[/TD]
[/TR]
[TR]
[TD]000[/TD]
[TD]Saira[/TD]
[TD]15.04[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Ahmad[/TD]
[TD]8.11[/TD]
[/TR]
[TR]
[TD]99999[/TD]
[TD]Rania[/TD]
[TD]7.02[/TD]
[/TR]
</tbody>[/TABLE]


If i make a Vlookup for LPO number (123) it will show me only Mary name and ignores Ahmad, how can i make it loop and understands that there is another name for the same LPO number that has to be shown too.

[TABLE="width: 300"]
<tbody>[TR]
[TD]Lpo Number[/TD]
[TD]Candidate Name[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ahmad[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

Two choices.
Formula in C11 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down. It works in all Excel versions.

Formula in B11 only works for Excel 2010 or later but does not require the special entry.

Excel Workbook
	A	B	C
1	Lpo Number	Candidate Name	Flight date
2	123	Mary	12.03
3	0	Saira	15.04
4	123	Ahmad	8.11
5	99999	Rania	7.02
6
7
8
9
10	Lpo Number	Candidate Name
11	123	Mary	Mary
12		Ahmad	Ahmad
13
Lookup

 

[Jwaied]

First i would like to thank you very much for the prompt response,

i couldn't do it , too hard for me.

ill try to upload a screen shot.

 

[Peter_SSs]

What version of Excel are you using?

 

[Jwaied]

2013

 

[Jwaied]

can i have your email address that i can sent it to you ?

 

[Peter_SSs]

can i have your email address that i can sent it to you ?

No. Refer to #4 of the Forum Rules

In what way did the suggestions in post #2 fail for you?

 

[Jwaied]

im sorry i just saw the forum rules,

i have never dealt with such a function, as i told u i was asking IF i can do that or not... fortunately i can as you explained. i was trying to analyze the function that you wrote but i could not figure it out.

just to explain a little bit, i built a tracker for our company LPO's, in order that it will be printed easily, so the printing page will be in a different sheet inside the same workbook, that has to take the information from the tracker which has the LPO number, candidate name, date, agency etc... i wont have any problems if i make an LPO for each name.. but sometimes we order multiple candidates on the same airline and air ticket, this is where i must put them all in one LPO number.

i understood that you wrote me a formula that functions as i requested, but can you make it more simple, or if you tell me what is this methodology so i can read about it.

both ways i appreciate your effort of helping me and i thank you very much.

 

[Jwaied]

for example, i used this formula and i analysed it, =IFERROR(INDEX(B$2:B$5,AGGREGATE(15,6,(ROW(B$2:B$5)-ROW(B$2)+1)/(A$2:A$5=A$11),ROWS(B$11:B11))),"")

last part is ,ROWS(B$11:B11) since that it is a formula on the same cell it shows me that i cannot select B11 as a cell on ROWS

still it is showing me 1 name, either mary or ahmad, how did u make B12 to show ahmad.

i'm using excel 2013.

 

[Jwaied]