Copy over cells from different sheet2 if name match

[evenyougreg]

In this example file, sheet1 is called List and sheet 2 is called VMs.

If there is a "Hostname" match between the 2 sheets, I would like to copy the "OS" cell and the "Env" cell over from "VMs" to the "List" sheet. If no match is found then it would be cool if something like "not found" was to fill the empty cells.

Thank you!!

example.xlsx
	A	B	C	D	E	F
1	Hostname	VM/Physical	Power State	OS	Location	Env
2	l-centtest01	VM	on		West
3	l-ubu01	VM	on		East
4	l-ubu02	VM	on		West
5	l-webapp01	VM	on		East
6	l-webapp02	VM	on		East
7	m-iisapp01	Physical	on		West
8	m-iisapp02	Physical	on		East
9	m-poc01	VM	on		East
10	m-poc02	VM	on		West
11	m-sqldb01	Physical	on		West
12	m-dsc01	VM	on		East
13	m-dsc02	VM	on		West
List

example.xlsx
	A	B	C
1	Hostname	OS	Env
2	l-centtest01	CentOS 7	prod
3	l-ubu01	Ubuntu 20.04	prod
4	l-ubu02	Ubuntu 20.04	test
5	l-webapp01	RHEL 7	prod
6	l-webapp02	RHEL 7	prod
7	m-iisapp01	Windows-2012ServerR2	prod
8	m-iisapp02	Windows-2012ServerR2	prod
9	m-poc01	Windows-2019Server	test
10	m-poc02	Windows-2019Server	test
11	m-sqldb01	Windows-2016Server	prod
12	m-sqldbtest	Windows-2016Server	test
VMs

 

[Fluff]

How about

=IFNA(INDEX(VMs!B2:B12,MATCH(H2,VMs!A2:A12,0)),"Not found")

 

[eastrand]

Here is another way using xlookup.

Book2
	A	B	C	D	E	F
1	Hostname	VM/Physical	Power State	OS	Location	Env
2	l-centtest01	VM	on	CentOS 7	West	prod
3	l-ubu01	VM	on	Ubuntu 20.04	East	prod
4	l-ubu02	VM	on	Ubuntu 20.04	West	test
5	l-webapp01	VM	on	RHEL 7	East	prod
6	l-webapp02	VM	on	RHEL 7	East	prod
7	m-iisapp01	Physical	on	Windows-2012ServerR2	West	prod
8	m-iisapp02	Physical	on	Windows-2012ServerR2	East	prod
9	m-poc01	VM	on	Windows-2019Server	East	test
10	m-poc02	VM	on	Windows-2019Server	West	test
11	m-sqldb01	Physical	on	Windows-2016Server	West	prod
12	m-dsc01	VM	on	Not Found	East	Not Found
13	m-dsc02	VM	on	Not Found	West	Not Found
List

Cell Formulas
Range	Formula
D2:D13	D2	=XLOOKUP(A2:A13,VMs!A2:A12,VMs!B2:B12,"Not Found")
F2:F13	F2	=XLOOKUP(A2:A13,VMs!A2:A12,VMs!C2:C12,"Not Found")
Dynamic array formulas.

 

[evenyougreg]

Here is another way using xlookup.

Book2
	A	B	C	D	E	F
1	Hostname	VM/Physical	Power State	OS	Location	Env
2	l-centtest01	VM	on	CentOS 7	West	prod
3	l-ubu01	VM	on	Ubuntu 20.04	East	prod
4	l-ubu02	VM	on	Ubuntu 20.04	West	test
5	l-webapp01	VM	on	RHEL 7	East	prod
6	l-webapp02	VM	on	RHEL 7	East	prod
7	m-iisapp01	Physical	on	Windows-2012ServerR2	West	prod
8	m-iisapp02	Physical	on	Windows-2012ServerR2	East	prod
9	m-poc01	VM	on	Windows-2019Server	East	test
10	m-poc02	VM	on	Windows-2019Server	West	test
11	m-sqldb01	Physical	on	Windows-2016Server	West	prod
12	m-dsc01	VM	on	Not Found	East	Not Found
13	m-dsc02	VM	on	Not Found	West	Not Found
List

Cell Formulas
Range	Formula
D2:D13	D2	=XLOOKUP(A2:A13,VMs!A2:A12,VMs!B2:B12,"Not Found")
F2:F13	F2	=XLOOKUP(A2:A13,VMs!A2:A12,VMs!C2:C12,"Not Found")
Dynamic array formulas.

That worked Sometimes the amount of rows changes week to week, is there a way to remove the hard coded row amount, and to run until it finds the end of the list?

 

[eastrand]

Just change the two formulas to:
=XLOOKUP(A2:A13,VMs!A:A,VMs!B:B,"Not Found")
=XLOOKUP(A2:A13,VMs!A:A,VMs!C:C,"Not Found")

 

[eastrand]

Or this to make it fully dynamic:
=XLOOKUP(INDIRECT("A2:A" & COUNTA(A:A)),VMs!A:A,VMs!B:B,"Not Found")
=XLOOKUP(INDIRECT("A2:A" & COUNTA(A:A)),VMs!A:A,VMs!C:C,"Not Found")

 

[evenyougreg]

Or this to make it fully dynamic:
=XLOOKUP(INDIRECT("A2:A" & COUNTA(A:A)),VMs!A:A,VMs!B:B,"Not Found")
=XLOOKUP(INDIRECT("A2:A" & COUNTA(A:A)),VMs!A:A,VMs!C:C,"Not Found")

Bingo, I can kinda see how it works so I can reuse it. Thanks so much!

 

[eastrand]

You're welcome. Thanks for your feedback.

 

[evenyougreg]

@eastrand I posted a more VBA focused thread involving your answer, if you are able to chime in I would appreciate it, if not no worries and thank you again.

Run formula in VBA until last row

I'm trying to get this formula to run from D2 until the last detected row, and without using hard coded numbers like D2 until D1243 or whatever because the dataset changes all the time. I've had issues in the past with this on another forum so I will try my luck here. the formula is...

www.mrexcel.com