Vlookup using isnumber and right

[mrwiley]

Good day,

I am stumped once again and I'd like to know if someone could share some incite to the realm of possibility.

I need to look up a part # based on a completely different number. However my "part #'s" have process codes at the end.

For example - part # 12-4567*05S goes with part number ABCDEF

I have a list of parts in Column A - but data set in "sheet2" which has in Column B the part I need to call but the "child part" with the *05S in column F with over 12K rows.

In the list Column F has all kinds of *412 *305 *601 parts but I only want the *05S parts.

I have tried =IF(ISNUMBER(SEARCH("8-*",Sheet2!$F:$F,1)),VLOOKUP('ASSEMBLY PART'!$A2,Sheet2!$C:$F,4,0),"") - it results in nothing (worked for another line but the dataset was different.

I have tried "OR(RIGHT($A19,3)={"05S","412","502","205"}))" but this only works for parts within the same sheet and doesn't look for parts in the data set.

I have tried - =IF(ISNUMBER(SEARCH("3-*",Sheet2!$F:$F,RIGHT(Sheet2!$F20:$F12018,3)="05S")),VLOOKUP('ASSEMBLY PART'!$A20,Sheet2!$C:$F,4,0),"") but it results in nothing.

My parts in Column F on sheet2 start with 3- 4- 8- and end with *05S etc.

Any ideas would be awesome.

 

[mrwiley]

Some sample data provided by private link due to sensitive data, but note that I will be responding publicly in the thread per #4 of the Forum Rules

Some comments/question:

1. Your data is very large so in due course I may suggest an alternative approach.

2. One of the main reasons your formulas are failing is that the data in Sheet2 goes down to nearly row 100,000 but the ranges in your formula only go to row 9,263. That is, the formulas are only looking in about 10% of the data.

3. The formula you have used in column C will also fail to return the correct result because you altered this part of my formula
=IFERROR(INDEX(Sheet2!F$2:F$5,AGGREGATE(15,6,ROW(Sheet2!F$2:F$5)-ROW(Sheet2!F$2)+1/((Sheet2!C$2:C$5=A2)*(RIGHT(Sheet2!F$2:F$5,4)="*05S")*((LEFT(Sheet2!F$2:F$5,2)="3-")+(LEFT(Sheet2!F$2:F$5,2)="4-"))),1)),"Not found")

In post 7 when you said "I want to call the 3- / 4- part numbers with *05S from Column F in Sheet2", I thought that meant you wanted to look for either a "3-..." part of a "4-... " part. What did that statement actually mean?

4. In column A of 'ASSEMBLY PART' you only have an assembly part number on every third row, with 0 values between. Do you actually want anything returned in columns C & D on the rows that contain 0 in column A? If so, what?

Good morning,

I will be reducing the amount of data which should help. I want to look for 3 part numbers which begin with 3- 4- or 8-

As for the assembly sheet - the cells are calling data from another sheet which changes monthly. So the 0's are blanks and mean nothing but if a part number pops in on the other sheet then data would populate.

In simple terms, i need to call data for the assembly which has 2 part #'s ending in *05S - either part for the assembly would be a 3- and 4- or a 3- and 8-

Thank you again for your continued support and efforts and most importantly your time.

 

[Peter_SSs]

2 part #'s ending in *05S - either part for the assembly would be a 3- and 4- or a 3- and 8-

Just to clarify, does that mean ..

Column C should return a part number definitely only starting with 3- (and ending with *05S), and

Column D should return a part number starting with either 4- OR 8- (and ending with *05S)

 

[mrwiley]

Just to clarify, does that mean ..

Column C should return a part number definitely only starting with 3- (and ending with *05S), and

Column D should return a part number starting with either 4- OR 8- (and ending with *05S)

Good afternoon,

My apologies for my delay - I have been busy with meetings and training. never ending but a good life.

Yes, Column C should pull 3 and Column D should pull 4 or 8 with *05S

Thanks again for the patience and willingness to assist. I can't tell you how beneficial this would be for the plant along with improving time efficiencies.

 

[Peter_SSs]

Yes, Column C should pull 3 and Column D should pull 4 or 8 with *05S

OK, try these. You mentioned reducing the size of your data. That would be helpful & you should reduce the $100000's in the formulas if possible but still cover the extent of the data in Sheet2.

C5: =IF(A5=0,"",IFERROR(INDEX(Sheet2!F$2:F$100000,AGGREGATE(15,6,ROW(Sheet2!F$2:F$100000)-ROW(Sheet2!F$2)+1/((Sheet2!C$2:C$100000=A5)*(RIGHT(Sheet2!F$2:F$100000,4)="*05S")*(LEFT(Sheet2!F$2:F$100000,2)="3-")),1)),"Not found"))

D5: =IF(A5=0,"",IFERROR(INDEX(Sheet2!F$2:F$100000,AGGREGATE(15,6,ROW(Sheet2!F$2:F$100000)-ROW(Sheet2!F$2)+1/((Sheet2!C$2:C$100000=A5)*(RIGHT(Sheet2!F$2:F$100000,4)="*05S")*((LEFT(Sheet2!F$2:F$100000,2)="4-")+(LEFT(Sheet2!F$2:F$100000,2)="8-"))),1)),"Not found"))

 

[mrwiley]

OK, try these. You mentioned reducing the size of your data. That would be helpful & you should reduce the $100000's in the formulas if possible but still cover the extent of the data in Sheet2.

C5: =IF(A5=0,"",IFERROR(INDEX(Sheet2!F$2:F$100000,AGGREGATE(15,6,ROW(Sheet2!F$2:F$100000)-ROW(Sheet2!F$2)+1/((Sheet2!C$2:C$100000=A5)*(RIGHT(Sheet2!F$2:F$100000,4)="*05S")*(LEFT(Sheet2!F$2:F$100000,2)="3-")),1)),"Not found"))

D5: =IF(A5=0,"",IFERROR(INDEX(Sheet2!F$2:F$100000,AGGREGATE(15,6,ROW(Sheet2!F$2:F$100000)-ROW(Sheet2!F$2)+1/((Sheet2!C$2:C$100000=A5)*(RIGHT(Sheet2!F$2:F$100000,4)="*05S")*((LEFT(Sheet2!F$2:F$100000,2)="4-")+(LEFT(Sheet2!F$2:F$100000,2)="8-"))),1)),"Not found"))

Simply Amazing, words can't express my gratitude for your skill and your time you've invested. This worked flawlessly! Thank you, thank you, thank you!

 

[Peter_SSs]

Simply Amazing, words can't express my gratitude for your skill and your time you've invested. This worked flawlessly! Thank you, thank you, thank you!

You are very welcome. Glad it has worked so well for you.