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]

When converting the formula to suit your layout you have ..
- Left out most of the $ signs. They are important.
- Changed that last ROWS() section of the formula to only refer to a single cell instead of a range.

For the layout you showed in post #10, put this formula in cell O32 and then copy it down several rows.

=IFERROR(INDEX(C$2:C$6,AGGREGATE(15,6,(ROW(C$2:C$6)-ROW(C$2)+1)/(B$2:B$6=N$32),ROWS(O$32:O32))),"")

Once you have done that, select cell O33 and you will see how that final ROWS() function has changed to now refer to 2 rows - ROWS(O$32:O33) - and that is how the whole formula knows to extract the 2nd relevant name.

 

[Jwaied]

IT WORKED !!!!!!
THANKS THANKS MAN YOU ARE AWESOME!!

can i recommend you on this website?

+ 1 more last question, if i analyze this formula, can i make it work on separate sheets?... like to take information from separate sheets, or all the information should be in the same sheet to receive a result.. please tell me it works.

 

[Peter_SSs]

IT WORKED !!!!!!
THANKS THANKS MAN YOU ARE AWESOME!!

You are welcome.

can i recommend you on this website?

What you wrote above is more than sufficient.

+ 1 more last question, if i analyze this formula, can i make it work on separate sheets?... like to take information from separate sheets, or all the information should be in the same sheet to receive a result.. please tell me it works.

The data table and the formula can certainly be on different sheets

Excel Workbook
	B	C
1	Lpo Number	Candidate Name
2	123	Mary
3	0	Saira
4	123	Ahmad
5	99999	Rania
6
Sheet 2

Excel Workbook
	N	O
32	123	Mary
33		Ahmad
34
Sheet 3

 

[olipar]

Hi

Im new in this forum, i wounder if its possible to get some kinde of loop with new search of the result in this formula so it keps looping until there is no morre finds?

B11	=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))),"")
C11	{=IFERROR(INDEX(B$2:B$5,SMALL(IF(A$2:A$5=A$11,ROW(B$2:B$5)-ROW(B$2)+1),ROWS(C$11:C11))),"")}

<tbody>
</tbody>

Ex. When i use this formula i only get result thats in E column but i want the result to be as in G column.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ART.NR[/TD]
[TD][/TD]
[TD][/TD]
[TD]Search for[/TD]
[TD]Result[/TD]
[TD][/TD]
[TD]Result[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]30[/TD]
[TD]40[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]40[/TD]
[TD][/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]30[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]30[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD][/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]30[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]30[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]13[/TD]
[TD][/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]40[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]40[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]40[/TD]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]71[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]50
[/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]82[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]50[/TD]
[TD]71[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]91[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]50[/TD]
[TD]82[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]92[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]50[/TD]
[TD]91[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]50[/TD]
[TD]92[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]