Pull Vehicle Information

[myletterboxnp]

Hi there, need help in creating a formula.

Sheet 1
A1 Employee Name, B1 Department, C1 Position
There are 500 records of employees

Sheet 2
A1 Employee Name, B1 Vehicle Make, C1 Vehicle Model, D1 Vehicle Color E1, Vehicle Plate Number
There are 200 records of employees

I need to pull the vehicle information (Make/ Model/ Color/ Plate Number) from Sheet 2 to Sheet 1 behind each Name in Sheet 1 for those records whose name match. (Out of 500 employees, 200 use vehicles)

What should be the formula in Sheet 1?

Kind Regards,

 

[DanteAmor]

Thanks for posting on the forum.

Try this formula:

Example Sheet1:

Dante Amor
	A	B	C	D	E	F	G
1	Employee Name	Department	Position	Make	Model	Color	Plate Number
2	J S	HR	1	Make2	Model2	Color2	Plate Number2
3	Dante Amor	Sist	2	Make3	Model3	Color3	Plate Number3
4	Other	xd	3	No exists	No exists	No exists	No exists
Sheet1

Cell Formulas
Range	Formula
D2:G4	D2	=IFERROR(VLOOKUP($A2,Sheet2!$A:$E,COLUMNS($C$1:D1),0),"No exists")

Example Sheet2

Dante Amor
	A	B	C	D	E
1	Employee Name	Make	Model	Color	Plate Number
2	J S	Make2	Model2	Color2	Plate Number2
3	Dante Amor	Make3	Model3	Color3	Plate Number3
Sheet2

Note XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[myletterboxnp]

Thank you for the invaluable help! This worked perfectly as I had wanted!

Kind Regards,

 

[AhoyNC]

Since you have Excel 365 another option would be to use the FILTER function.
Using the example that Dante was kind enough to make:

Book1
	A	B	C	D	E	F	G
1	Employee Name	Department	Position	Make	Model	Color	Plate Number
2	J S	HR	1	Make2	Model2	Color2	Plate Number2
3	Dante Amor	Sist	2	Make3	Model3	Color3	Plate Number3
4	Other	xd	3	No exists
Sheet1

Cell Formulas
Range	Formula
D4,D2:G3	D2	=FILTER(Sheet2!$B$2:$E$3,$A2=Sheet2!$A$2:$A$3,"No exists")
Dynamic array formulas.

Book1
	A	B	C	D	E
1	Employee Name	Make	Model	Color	Plate Number
2	J S	Make2	Model2	Color2	Plate Number2
3	Dante Amor	Make3	Model3	Color3	Plate Number3
Sheet2