Lookup If Blank Or only have certain value

SColyn

New Member
Joined
May 12, 2016
Messages
17
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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:

SColyn

New Member
Joined
May 12, 2016
Messages
17
Hi,

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

Thank you for helping
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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:

SColyn

New Member
Joined
May 12, 2016
Messages
17

ADVERTISEMENT

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

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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
 

SColyn

New Member
Joined
May 12, 2016
Messages
17

ADVERTISEMENT

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>
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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...
 

SColyn

New Member
Joined
May 12, 2016
Messages
17
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.
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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:

Watch MrExcel Video

Forum statistics

Threads
1,112,785
Messages
5,542,503
Members
410,559
Latest member
jordansmith6532
Top