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.

 

[mole999]

how about this

if you are going to search for 3- then it is actually NOT a number

ISTEXT would work

looking at 3-111 with this will force =ISNUMBER(--LEFT(E10,1)) to TRUE, but only because I am using 1 digit and not 2

 

[mrwiley]

how about this

if you are going to search for 3- then it is actually NOT a number

ISTEXT would work

looking at 3-111 with this will force =ISNUMBER(--LEFT(E10,1)) to TRUE, but only because I am using 1 digit and not 2

Thank you, this didn't work... L (

 

[Peter_SSs]

if you are going to search for 3- then it is actually NOT a number

ISTEXT would work

Although "3-" is not a number, SEARCH("3-*",.. will be a number (or error), but never Text


mrwiley
From your description I cannot understand well enough your layout or requirement.
Any chance you could post a small set of dummy data from each worksheet (& the expected results) and explain again in relation to that sample data?
My signature block below has a link to help with how you can do that so we can copy/paste your sample data to test with.

 

[mrwiley]

Although "3-" is not a number, SEARCH("3-*",.. will be a number (or error), but never Text


mrwiley
From your description I cannot understand well enough your layout or requirement.
Any chance you could post a small set of dummy data from each worksheet (& the expected results) and explain again in relation to that sample data?
My signature block below has a link to help with how you can do that so we can copy/paste your sample data to test with.

OK, I need to lookup ABC372C and only find the part in column F with a *05S (Column F has many different parts *412 *502 *601 *MAT) - each part will have 2 *05S numbers (right part number and left part number but go into 1 assembly) What you don't see is the 4- part number.

Hope this makes it easier. Thank you

[TABLE="width: 509"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]BMLITM[/TD]
[TD]BMUM[/TD]
[TD]BMUNCS[/TD]
[TD]BMAITM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ABC372C[/TD]
[TD]EA[/TD]
[TD="align: right"][/TD]
[TD]3-131#EBJ23M*05S[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ABC372C[/TD]
[TD]EA[/TD]
[TD="align: right"][/TD]
[TD]8-725#PTJ61*05S[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ABDC168[/TD]
[TD]EA[/TD]
[TD="align: right"][/TD]
[TD]3-555#EBJ797M*05S[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ABDC168[/TD]
[TD]EA[/TD]
[TD="align: right"][/TD]
[TD]8-333#ETJ644*05S[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Hope this makes it easier.

Well, I'm not sure - it didn't quite fit the request of ..

.. data from each worksheet (& the expected results)

Anyway, see if this is the sort of thing you want.
If it isn't, which is quite likely, please give samples from both sheets and the expected results and make sure it is clear which is which.

Formula copied down.

Excel Workbook
	C	D	E	F	G	H	I
1	BMLITM	BMUM	BMUNCS	BMAITM
2	ABC372C	EA		3-131#EBJ23M*05S		ABC372C	3-131#EBJ23M*05S
3	ABC372C	EA		8-725#PTJ61*05S		ABDC168	3-555#EBJ797M*05S
4	ABDC168	EA		3-555#EBJ797M*05S
5	ABDC168	EA		8-333#ETJ644*05S
Sheet3

 

[mrwiley]

Thank you for taking the time to work on this...

I need to call data from the "Sheet2" tab into the "Assembly Part" tab within the workbook.

In Column A on "assembly part" tab I have the assemblies. in Column C "assembly part" tab I want to call the 3- / 4- part numbers with *05S from Column F in Sheet2 tab by using reference to the assembly in Column C in Sheet2 tab. In Column D I want to call 8- part numbers with *05S

The example I provided earlier was Sheet2 data.

I don't know how to get a spread-sheet added to the post without sharing a link from Google Drive. Not sure if this is allowed.

Thanks

 

[mole999]

you cannot add files, but posting a sample file on a free reputable file share site is acceptable

 

[Peter_SSs]

I don't know how to get a spread-sheet added to the post without sharing a link from Google Drive. Not sure if this is allowed.

That would be allowed but many helpers will not download such files or are prevented from doing so but workplace security restrictions.
Better is to post small copyable screen shots as I have done (my signature block below has help on that) or even what you did in post 5 would have done for 'Assembly Part' as well.

Anyway, see if this is better. Note that I have altered the Sheet2 sample data a little and each formula in 'Assembly part is copied down.

Excel 2016
	C	D	E	F
1	BMLITM	BMUM	BMUNCS	BMAITM
2	ABC372C	EA		3-131#EBJ23M*412
3	ABC372C	EA		8-725#PTJ61*05S
4	ABDC168	EA		4-555#EBJ797M*05S
5	ABDC222	EA		8-333#ETJ644*05S
Sheet2

Excel 2016
	A	B	C	D
1			3-/4-	8-
2	ABC372C		Not found	8-725#PTJ61*05S
3	ABDC168		4-555#EBJ797M*05S	Not found
4	ABDC222		Not found	8-333#ETJ644*05S
Assembly Part

Cell Formulas
Range	Formula
C2	=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")
D2	=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)="8-")),1)),"Not found")

 

[Peter_SSs]

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?