Critical V-Lookup function

[sshrikanth2]

Dear All,</SPAN>

I need your help!!!</SPAN>
I have two workbooks namely – "User List" and "Master List" (Table shown below). I am using the Vlookup function to search for a value in the first column of a table array and return a value in the same row from another column in the table array. But, the problem here is that there are multiple car brands for some of the employees, say for example “Mark Waugh” who possess two cars namely Swift and Ferrari but when I apply Vlookup function in the Column B of User list workbook it shows Swift. I have added 3 columns for car brands module in the user list. I need an advanced Vlookup formula to show all car brands possessed by an employee in different columns namely Car brand, Car brand1, Car brand2.</SPAN>

Thanking you in anticipation.</SPAN>



User List</SPAN>

A B C D E F</SPAN>

Emp Name</SPAN></SPAN>	Car Brands</SPAN></SPAN>	Car Brands1</SPAN></SPAN>	Car Brands2</SPAN></SPAN>	Location</SPAN></SPAN>	FIN</SPAN></SPAN>
Sulemaan</SPAN></SPAN>
Arpitha</SPAN></SPAN>
Anushka Sharma</SPAN></SPAN>
LK Ball</SPAN></SPAN>
Bose DK</SPAN></SPAN>
Madar CH</SPAN></SPAN>
Mangan saman</SPAN></SPAN>
Manjunath</SPAN></SPAN>
Venkatesh</SPAN></SPAN>
Steve Waugh</SPAN></SPAN>
Saman Singh</SPAN></SPAN>
Mark Waugh</SPAN></SPAN>
Srinath</SPAN></SPAN>

<TBODY>
</TBODY>

Master List</SPAN>

A B C D</SPAN>

Emp Name</SPAN></SPAN>	Car Brands</SPAN></SPAN>	Location</SPAN></SPAN>	FIN</SPAN></SPAN>
Sulemaan</SPAN></SPAN>	Punto</SPAN></SPAN>	West Indies</SPAN></SPAN>	725700</SPAN></SPAN>
Azhar</SPAN></SPAN>	Zen</SPAN></SPAN>	Australia</SPAN></SPAN>	1610934</SPAN></SPAN>
Anushka Sharma</SPAN></SPAN>	Punto</SPAN></SPAN>	Australia</SPAN></SPAN>	1613036</SPAN></SPAN>
LK Ball</SPAN></SPAN>	Zen</SPAN></SPAN>	India</SPAN></SPAN>	1613036</SPAN></SPAN>
Bose DK</SPAN></SPAN>	Santro</SPAN></SPAN>	Australia</SPAN></SPAN>	1611724</SPAN></SPAN>
Madar CH</SPAN></SPAN>	Swift</SPAN></SPAN>	Australia</SPAN></SPAN>	725700</SPAN></SPAN>
Mark Waugh</SPAN></SPAN>	Swift</SPAN></SPAN>	China</SPAN></SPAN>	716073</SPAN></SPAN>
Manjunath</SPAN></SPAN>	MBENZ</SPAN></SPAN>	Uganda</SPAN></SPAN>	958667</SPAN></SPAN>
Venkatesh</SPAN></SPAN>	Punto</SPAN></SPAN>	Japan</SPAN></SPAN>	722823</SPAN></SPAN>
Steve Waugh</SPAN></SPAN>	Punto</SPAN></SPAN>	France</SPAN></SPAN>	722494</SPAN></SPAN>
Saman Singh</SPAN></SPAN>	Punto</SPAN></SPAN>	Australia</SPAN></SPAN>	716073</SPAN></SPAN>
Mark Waugh</SPAN></SPAN>	Ferrari</SPAN></SPAN>	Germany</SPAN></SPAN>	720319</SPAN></SPAN>
Bose DK</SPAN></SPAN>	Santro</SPAN></SPAN>	Australia</SPAN></SPAN>	1611724</SPAN></SPAN>
Madar CH</SPAN></SPAN>	Swift</SPAN></SPAN>	Australia</SPAN></SPAN>	725700</SPAN></SPAN>
Mangan saman</SPAN></SPAN>	MBENZ</SPAN></SPAN>	China</SPAN></SPAN>	716073</SPAN></SPAN>
LK Ball</SPAN></SPAN>	Santro</SPAN></SPAN>	India</SPAN></SPAN>	1610934</SPAN></SPAN>
Saman Singh</SPAN></SPAN>	Swift</SPAN></SPAN>	Australia</SPAN></SPAN>	724369</SPAN></SPAN>
Srinath</SPAN></SPAN>	Indica</SPAN></SPAN>	India</SPAN></SPAN>	725700</SPAN></SPAN>
Anushka Sharma</SPAN></SPAN>	Punto</SPAN></SPAN>	Australia</SPAN></SPAN>	1613036</SPAN></SPAN>
Steve Waugh</SPAN></SPAN>	Zen</SPAN></SPAN>	India</SPAN></SPAN>	716073</SPAN></SPAN>

<TBODY>
</TBODY>

Regards,

Srikanth M

 

[hippiehacker]

if your data is from A1:D21 then you could use
for the second match you can use the following array formula

=INDEX($A$1:$D$21;SMALL(IF(OFFSET($A$1:$D$21;0;0;ROWS($A$1:$D$21);1)=G1;ROW(OFFSET($A$1:$D$21;0;0;ROWS($A$1:$D$21);1))-ROW(OFFSET($A$1:$D$21;0;0;1;1))+1;ROW(OFFSET($A$1:$D$21;ROWS($A$1:$D$21)-1;0;1;1))+1);2);2)

and for the third you can use

=INDEX($A$1:$D$21;SMALL(IF(OFFSET($A$1:$D$21;0;0;ROWS($A$1:$D$21);1)=G1;ROW(OFFSET($A$1:$D$21;0;0;ROWS($A$1:$D$21);1))-ROW(OFFSET($A$1:$D$21;0;0;1;1))+1;ROW(OFFSET($A$1:$D$21;ROWS($A$1:$D$21)-1;0;1;1))+1);3);2)

dont forget to enter the formula with ctrl-shift-enter