Lookup different fields and return data

[ANARK]

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.

[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!

[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 · Accepted solution]

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

[ANARK]

This worked, thanks for the help.