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

Asset Type	Asset Code	Customer Plant
648536-0500	90000045698	DZC-NCA Rou
648536-0500		DZC
648536-0500		ZAC
648582-0100	900000789625	DZC-NCA KZN
658389-0100		ZAC

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Sheet 2 data - Master data

Asset Type	Asset Code	Customer Plant
648536-0500	90000045698	DZC-NCA Rou
648536-0500	90000068725	DZC-RTH NPA
648536-0500	90000016792	ZAC-KLO JHB
648536-0500	90000002267	PKC-SHA 001
648536-0500	90000068911	TRC-TRK 582
648536-0500	90000002650	DZC-SARL CLAT ORN
658389-0100	90000052740	ZAC-PKC DBN
658389-0100	90000696701	ZAC-CLA JHB
648582-0100	90000789625	DZC-NCA KZN
648582-0100	90000092369	ZAC-RHO JHB

<colgroup><col><col><col></colgroup><tbody>
</tbody>

The result should look like this:

Asset Type	Asset Code	Customer Plant	Asset 1	Asset 2	Asset 3
648536-0500	90000045698	DZC-NCA Rou
648536-0500		DZC	90000045698	90000068725	90000002650
648536-0500		ZAC	90000016792
648582-0100	900000789625	DZC-NCA KZN
658389-0100		ZAC	90000052740	90000696701

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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.