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
[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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
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:

[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]
 
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,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

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