Hi Team - Need help in creating this formula or logic. I have data coming from 2 sheets, need to check data based on multiple columns from sheet1 and lookup in sheet2 and return the final data. In below sample, Sheet1: Lvl1-3 need to be used for lookup. if Lvl1 is blank or not found in sheet2, need to use Lvl2 or Lvl3 to get final data. ProdLvl1Lvl2Lvl3Return ValueAAPJ BAMER CEMEA D ID A MX B DE C East D Mid A West BAPJID Sheet2: Lvl1Lvl2Lvl3Return ValueAPJ ABAMER BCEMEA AC ID AB MX BC DE AC EastAB MidBC WestACAPJID AB Expected output: ProdLvl1Lvl2Lvl3Return ValueAAPJ ABBAMER BCCEMEA ACD ID ABA MX BCB DE ACC EastABD MidBCA WestACBAPJID AB Cant use join for this scenario, so looking for any other options. Looking for some help here, Thanks.

Lookup different fields and return data

Hi Team - Need help in creating this formula or logic. I have data coming from 2 sheets, need to check data based on multiple columns from sheet1 and lookup in sheet2 and return the final data.

In below sample,

Sheet1: Lvl1-3 need to be used for lookup. if Lvl1 is blank or not found in sheet2, need to use Lvl2 or Lvl3 to get final data.

Prod	Lvl1	Lvl2	Lvl3	Return Value
A	APJ
B	AMER
C	EMEA
D		ID
A		MX
B		DE
C			East
D			Mid
A			West
B	APJ	ID

Sheet2:

Lvl1	Lvl2	Lvl3	Return Value
APJ			AB
AMER			BC
EMEA			AC
	ID		AB
	MX		BC
	DE		AC
		East	AB
		Mid	BC
		West	AC
APJ	ID		AB

Expected output:

Prod	Lvl1	Lvl2	Lvl3	Return Value
A	APJ			AB
B	AMER			BC
C	EMEA			AC
D		ID		AB
A		MX		BC
B		DE		AC
C			East	AB
D			Mid	BC
A			West	AC
B	APJ	ID		AB

Cant use join for this scenario, so looking for any other options.

Looking for some help here, Thanks.

Transformation

Preparation

Data Investigation

Accepted answers

nagakavyasri

@ANARK Find & Replace tool is basically like lookup in excel. Check if this works:

Screenshot 2024-11-26 125529.png

11-26-24.1.yxmd

All comments

CoG

This is a simple process but requires some out of the box thinking! By Transposing the data and removing null values (from at least one of the data sets), you can identify all available joins, and then use the Unique Tool to remove extraneous joins (since we want the value for lowest "Lvl" join):

Hope this helps and Happy Solving!

Sample Workflow.yxmd

ANARK

Thanks for this, but its a little difficult to implement transpose in my scenario. Also Im just trying to mirror excel functionality like lookup, any other suggestions or way to do that?

Appreciate your help.

Bobbyt23

You could create a concat column on both sheets but replace blank with a character that wouldn’t appear. A pipe for instance.

e.g first row would be APJ||

then you could just use the find replace tool to do a lookup to the concat on the second sheet and append the return value to the first sheet.

ANARK

In actual dataset, Sheet1 doesnt have blanks. Sheet2 only has blanks thats the reason lookup is required.

First lookup is based on Lvl1 - if there is a match in Sheet2 then "Return Value" needs to be considered, if not found or blank then need to lookup based on 2 & 3 and then capture "Return Value".

nagakavyasri

@ANARK Find & Replace tool is basically like lookup in excel. Check if this works:

Screenshot 2024-11-26 125529.png

11-26-24.1.yxmd

ANARK

This worked, thanks for the help.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703