Help on XLOOKUP

[amim79]

I have a couple of sheets and I want to do an xlookup based on 2 columns in a separate sheet.

The first sheet is called 'Databases Inventory' with a sample data.

SQL Server Name	Database Name	Env	Type	Corresponding PRD SQL Cluster	on-prem DB version	Has OGG	In SQL Migration Scope	Kyndryl Scope	Has SQL MI Compatibility issue (y/n)	Size (GB)
KULDCPWSQL05	aiaIMGdb_2013	PRD	Single SQL Server	N/A - Standalone	SQL 2016	y	y	y		6.74

My destination sheet is called Timeline v5 with a sample data

On-prem	Cloud	Database name
SUMYFAP00022	SQL on VM - FAP22 UAT	aiaIMGdb_2013	Chee Wun	digital app, eCorr, NBFE, PrintAgent, etc	3.12
KULDCPWSQL05	SQL on VM - SQL05 PRD	aiaIMGdb_2013	Chee Wun	digital app, eCorr, NBFE*etc	6.74

I tried to do =XLOOKUP(C41,'Databases Inventory'!$B$2:$B$753,'Databases Inventory'!$I$2:$I$753) unfortunately it flagged both items. How can I select this correctly e.g. having size as a secondary lookup value from XLOOKUP?

 

[Alex Blakenburg]

See if this gives you what you are after.

Book6
	A	B	C	D	E	F	G
40	On-prem	Cloud	Database name
41	SUMYFAP00022	SQL on VM - FAP22 UAT	aiaIMGdb_2013	Chee Wun	digital app, eCorr, NBFE, PrintAgent, etc	3.12	Not Found
42	KULDCPWSQL05	SQL on VM - SQL05 PRD	aiaIMGdb_2013	Chee Wun	digital app, eCorr, NBFE*etc	6.74	y
Timeline v5

Cell Formulas
Range	Formula
G41:G42	G41	=XLOOKUP(1,('Databases Inventory'!$B$2:$B$753=C41)*('Databases Inventory'!$K$2:$K$753=F41),'Databases Inventory'!$I$2:$I$753,"Not Found")

 

[amim79]

Hi Alex, it is what I am looking for. I sent over the my other modified sheet, and wondering why it didn't work. It gave me some time to study and formula and few tweaks here and there it worked! Thanks Alex the Wizard. You made my day!

 

[Alex Blakenburg]

You're most welcome. Glad I could help.