Lookup If Blank Or only have certain value

[SColyn]

Good day,

I have a sheet with Asset Type code, Customer plant and Asset Number. The Asset Type code indicates the configuration on the machines and is not a unique number. The Customer plant is also not unique but Asset Number is unique. There can be numerous Asset numbers for one Asset Type code. I would like to include an Asset Number in the sheet horizontally (in columns, in the same row), I have a Master data sheet with all our Asset numbers, Customer Plant and Asset Type code.

I have tried some of the formulas but cannot get the result I am looking for.

The problem I am facing is, I only want to include the Asset number if the Asset Number field is blank and the Customer Plant field does not have a complete location. The locations which are incomplete with only have 3 letters (ZAC / SAC / IRC / EGC / PKC etc.)

Sheet 1 data
[TABLE="width: 348"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Asset Type[/TD]
[TD]Asset Code[/TD]
[TD]Customer Plant[/TD]
[/TR]
[TR]
[TD]648536-0500[/TD]
[TD]90000045698[/TD]
[TD]DZC-NCA Rou[/TD]
[/TR]
[TR]
[TD]648536-0500[/TD]
[TD] [/TD]
[TD]DZC[/TD]
[/TR]
[TR]
[TD]648536-0500[/TD]
[TD] [/TD]
[TD]ZAC[/TD]
[/TR]
[TR]
[TD]648582-0100[/TD]
[TD]900000789625[/TD]
[TD]DZC-NCA KZN[/TD]
[/TR]
[TR]
[TD]658389-0100[/TD]
[TD] [/TD]
[TD]ZAC
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 data - Master data
[TABLE="width: 348"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Asset Type[/TD]
[TD]Asset Code[/TD]
[TD]Customer Plant[/TD]
[/TR]
[TR]
[TD]648536-0500[/TD]
[TD]90000045698[/TD]
[TD]DZC-NCA Rou[/TD]
[/TR]
[TR]
[TD]648536-0500[/TD]
[TD]90000068725[/TD]
[TD]DZC-RTH NPA[/TD]
[/TR]
[TR]
[TD]648536-0500[/TD]
[TD]90000016792[/TD]
[TD]ZAC-KLO JHB[/TD]
[/TR]
[TR]
[TD]648536-0500[/TD]
[TD]90000002267[/TD]
[TD]PKC-SHA 001[/TD]
[/TR]
[TR]
[TD]648536-0500[/TD]
[TD]90000068911[/TD]
[TD]TRC-TRK 582[/TD]
[/TR]
[TR]
[TD]648536-0500[/TD]
[TD]90000002650[/TD]
[TD]DZC-SARL CLAT ORN[/TD]
[/TR]
[TR]
[TD]658389-0100[/TD]
[TD]90000052740[/TD]
[TD]ZAC-PKC DBN[/TD]
[/TR]
[TR]
[TD]658389-0100[/TD]
[TD]90000696701[/TD]
[TD]ZAC-CLA JHB[/TD]
[/TR]
[TR]
[TD]648582-0100[/TD]
[TD]90000789625[/TD]
[TD]DZC-NCA KZN[/TD]
[/TR]
[TR]
[TD]648582-0100[/TD]
[TD]90000092369[/TD]
[TD]ZAC-RHO JHB[/TD]
[/TR]
</tbody>[/TABLE]

The result should look like this:
[TABLE="width: 713"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Asset Type[/TD]
[TD]Asset Code[/TD]
[TD]Customer Plant[/TD]
[TD]Asset 1[/TD]
[TD]Asset 2[/TD]
[TD]Asset 3[/TD]
[/TR]
[TR]
[TD]648536-0500[/TD]
[TD]90000045698[/TD]
[TD]DZC-NCA Rou[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]648536-0500[/TD]
[TD] [/TD]
[TD]DZC[/TD]
[TD]90000045698[/TD]
[TD]90000068725[/TD]
[TD]90000002650[/TD]
[/TR]
[TR]
[TD]648536-0500[/TD]
[TD] [/TD]
[TD]ZAC[/TD]
[TD]90000016792[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]648582-0100[/TD]
[TD]900000789625[/TD]
[TD]DZC-NCA KZN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]658389-0100[/TD]
[TD] [/TD]
[TD]ZAC[/TD]
[TD]90000052740[/TD]
[TD]90000696701[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help will be greatly appreciated.
Susan

 

[jasonb75]

This looks like the same question that you asked here https://www.mrexcel.com/forum/excel-questions/1112518-lookup-if-blank-only-have-certain-value.html

 

[Peter_SSs]

Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

Any bumps, clarifications, or follow-ups should be posted to the linked thread.