Lookup If Blank Or only have certain value

Status
Not open for further replies.

SColyn

New Member
Joined
May 12, 2016
Messages
19
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 TypeAsset CodeCustomer Plant
648536-050090000045698DZC-NCA Rou
648536-0500 DZC
648536-0500 ZAC
648582-0100900000789625DZC-NCA KZN
658389-0100 ZAC

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

Sheet 2 data - Master data
Asset TypeAsset CodeCustomer Plant
648536-050090000045698DZC-NCA Rou
648536-050090000068725DZC-RTH NPA
648536-050090000016792ZAC-KLO JHB
648536-050090000002267PKC-SHA 001
648536-050090000068911TRC-TRK 582
648536-050090000002650DZC-SARL CLAT ORN
658389-010090000052740ZAC-PKC DBN
658389-010090000696701ZAC-CLA JHB
648582-010090000789625DZC-NCA KZN
648582-010090000092369ZAC-RHO JHB

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

The result should look like this:
Asset TypeAsset CodeCustomer PlantAsset 1Asset 2Asset 3
648536-050090000045698DZC-NCA Rou
648536-0500 DZC900000456989000006872590000002650
648536-0500 ZAC90000016792
648582-0100900000789625DZC-NCA KZN
658389-0100 ZAC9000005274090000696701

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

Any help will be greatly appreciated.
Susan
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,606
Office Version
  1. 365
Platform
  1. Windows
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.
 
Status
Not open for further replies.

Forum statistics

Threads
1,141,309
Messages
5,705,675
Members
421,404
Latest member
Mikecollo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top