Lookup If Blank Or only have certain value

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, I have a Master data sheet with all our Asset numbers, Customer Plant and Asset Type code.

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.)


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

Please assist.
Sheet 1
Asset TypeAsset CodeCustomer Plant
648536-050090000045698DZC-NCA Rou
648536-0500
<strike></strike>
<strike></strike>
DZC
648536-0500
<strike></strike>
<strike></strike>
ZAC
648582-0100
900000789625
<strike></strike>
DZC-NCA KZN

<tbody>
</tbody>
<strike></strike>
Sheet 2
Asset Type<strike></strike>
Asset Code<strike></strike>
Customer Plant
648536-0500
<strike></strike>
<strike></strike>
90000045698
<strike></strike>
<strike></strike>
DZC-NCA Rou
<strike></strike>
<strike></strike>
648536-0500
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>
<strike></strike>
90000068725
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>
DZC-RTH NPA
648536-0500
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>
<strike></strike>
90000016792
ZAC-KLO JHB
648536-0500
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>
<strike></strike>
90000002267
PKC-SHA 001
648536-0500
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>
<strike></strike>
90000068911
TRC-TRK 582
658389-0100
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>
<strike></strike>
90000052740
ZAC-PKC DBN
648582-0100
<strike></strike>
<strike></strike>
900000789625
<strike></strike>
<strike></strike>
DZC-NCA KZN
<strike></strike>

<tbody>
</tbody>
<strike></strike>
Any assistance would be greatly appreciated.
Susan
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
So what would be the output for

648536-0500 DZC

90000045698 or 90000068725 or 900000789625?
Or all of them?

You say "horizontally", I take that to mean in separate columns on the row they relate to, not in one cell.
 
Last edited:
Upvote 0
Hi,

All the Asset codes must be in separate columns in the same row.

Thank you for helping
 
Upvote 0
648536-0500 DZC

Does DZC mean list all the assets codes for which the Asset Type is 648536-0550 and the Customer Plant begins DZC ?
Or is there some other logic?
 
Last edited:
Upvote 0
Hi,

This data is related to machine configuration changes which we call Task Update requests.

We have different country codes, for instance, DZC, ZAC, SAC, JOC, TRC etc. The Customer Plant always starts with the country code and then plant location but where there are only DZC or ZAC, the Task Update request is applicable to all the e.g. 648536-0500 Asset Types. Many of our customers will have this machine type but each machine will have a unique serial number (Asset Code).
 
Upvote 0
IN d3
=IF(B3<>"",B3,INDEX(B$10:B$16,MATCH(A3,IF(C3=LEFT(C$10:C$16,3),A$10:A$16,),0),1))
and copy down the column

Use the results in column D
 
Upvote 0
Hi,

Thank you for the formula is works but it only gives me the first asset code, it is not giving me all the related asset codes if I drag the formula to the others columns on the right.

The result I am looking for is:

Asset TypeAsset CodeCustomer Plant
648536-0500 DZC9000004569890000068725

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

But the result was:
Asset TypeAsset CodeCustomer Plant
648536-0500 DZC90000045698 DZC90000045698 DZC90000045698

<colgroup><col><col><col span="6"></colgroup><tbody>
</tbody>

Is there a formula that would give me the desired result?

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>
 
Upvote 0
Oops! I set my test data up different to yours, all on the same sheet.
Didn't spot the horizontal output in your otriginal description.

I have this now, reflecting your original data

=IFERROR(INDEX(Sheet2!$B$2:$B$8,1,AGGREGATE(15,6,COLUMN(Sheet2!$B$2:$B$8)/(($A$2:$A$5=Sheet2!A$2:A$8)*(LEFT(C$2,3)=LEFT(Sheet2!C$2,3))),ROWS(A$1:A1))-(2-1)),"")

but it still doesn't work.
This might be a bit too complex for me to solve, I'll have another go though...
 
Upvote 0
Hi, I came across this formula but it is also not working perfectly.

=IF(C3="",INDEX(B$10:B$17,SMALL(IF(AND($A$10:$A$17=$B3,D3=LEFT($C$10:$C$17,3)),ROW($A$10:$A$17)-ROW($B$3)+1),COLUMNS($H3:H3))))

Could you look at this and see how to change it to give the desired result.
 
Upvote 0
I dont think AND will work there.
You'd need to put it as

(($A$10:$A$17=$B3)*(D3=LEFT($C$10:$C$17,3)))

Also that's an array formula so you'd need to enter it as Ctrl-Shift-Enter
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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
Back
Top