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, 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
​

[TABLE="class: grid, width: 500, align: center"]
<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​

<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD]DZC[/TD]
[/TR]
[TR]
[TD]

648536-0500​

<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD]ZAC[/TD]
[/TR]
[TR]
[TD]648582-0100[/TD]
[TD]

900000789625​

[/TD]
[TD]<strike></strike>

DZC-NCA KZN​

[/TD]
[/TR]
</tbody>[/TABLE]

<strike></strike>
​

Sheet 2
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Asset Type<strike></strike>
[/TD]
[TD]Asset Code<strike></strike>
[/TD]
[TD]Customer Plant[/TD]
[/TR]
[TR]
[TD]

648536-0500​

<strike></strike>
[/TD]
[TD]<strike></strike>

90000045698​

<strike></strike>
[/TD]
[TD]<strike></strike>

DZC-NCA Rou​

<strike></strike>
[/TD]
[/TR]
[TR]
[TD]<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>
[/TD]
[TD]<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>
[/TD]
[TD]DZC-RTH NPA[/TD]
[/TR]
[TR]
[TD]

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>
[/TD]
[TD]<strike></strike>

90000016792​

[/TD]
[TD]ZAC-KLO JHB[/TD]
[/TR]
[TR]
[TD]

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>
[/TD]
[TD]<strike></strike>

90000002267​

[/TD]
[TD]PKC-SHA 001[/TD]
[/TR]
[TR]
[TD]

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>
[/TD]
[TD]<strike></strike>

90000068911​

[/TD]
[TD]TRC-TRK 582[/TD]
[/TR]
[TR]
[TD]

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>
[/TD]
[TD]<strike></strike>

90000052740​

[/TD]
[TD]ZAC-PKC DBN[/TD]
[/TR]
[TR]
[TD]

648582-0100​

<strike></strike>
[/TD]
[TD]<strike></strike>

900000789625​

<strike></strike>
[/TD]
[TD]<strike></strike>

DZC-NCA KZN​

<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
Any assistance would be greatly appreciated.
Susan

 

[Special-K99]

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.

 

[SColyn]

Hi,

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

Thank you for helping

 

[Special-K99]

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?

 

[SColyn]

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]

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]

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:

[TABLE="width: 537"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Asset Type[/TD]
[TD]Asset Code[/TD]
[TD]Customer Plant[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]648536-0500[/TD]
[TD] [/TD]
[TD]DZC[/TD]
[TD="align: right"]90000045698[/TD]
[TD="align: right"]90000068725
[/TD]
[/TR]
</tbody>[/TABLE]

But the result was:
[TABLE="width: 855"]
<colgroup><col><col><col span="6"></colgroup><tbody>[TR]
[TD]Asset Type[/TD]
[TD]Asset Code[/TD]
[TD]Customer Plant[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]648536-0500[/TD]
[TD] [/TD]
[TD]DZC[/TD]
[TD="align: right"]90000045698[/TD]
[TD] DZC[/TD]
[TD="align: right"]90000045698[/TD]
[TD] DZC[/TD]
[TD="align: right"]90000045698[/TD]
[/TR]
</tbody>[/TABLE]

Is there a formula that would give me the desired result?
[TABLE="width: 537"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

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]

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]

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