Vlookup with partial match or use VBA

[lucky07]

Hello,
I am trying to use vlookup to pull data from one sheet into another. Presently I have had to have the formula ignore part numbers longer than 11 characters and drop the 12 character in order to get it to work.

=IF(OR((VLOOKUP(LEFT(C13,11) & "*",MTBPR!A:H,3,FALSE))="N/A",(VLOOKUP(LEFT(C13,11) & "*",MTBPR!A:H,3,FALSE)="x")),0,VLOOKUP(LEFT(C13,11) & "*",MTBPR!A:H,3,FALSE))

The above snippet will take a reference number located on the main sheet and will find it on a different sheet and then pull the data, a few columns over, into the sheet. It will ignore some of the data in case of N/A (not #N/A) or x in that field.

The problem is I have some PN's that look like: 103-053-100A and others like: 303-141-100A-01.

I would like to have a single formula or bit of code that could handle both cases. If more information would be helpful let me know.
Thanks.

 

[borgem]

It looks like it should handle both, I am not sure what the problem is...what is it NOT doing that it should? Is it supposed to specifically look for 303-141-100A-01 instead of 303-141-100A if 303-141-100A-01 is referenced?

 

[lucky07]

Sorry for the delay and thank you for the reply.

There are PN's with different lengths and I would like it to find the closest match and then lookup the necessary data next to that match. Presently with the left(C13,11) it only looks up 11 characters. I am unsure of a more universal way to implement it that will find the closest possible match.

Also, Does the list need to be ordered or can I reference an unordered list.

Thanks.

 

[borgem]

Order shouldn't matter, but the asterisk seems to be doing the trick for anything over 11 characters...unless you have PN's whose first 11 are identical and you want to be able to handle those situations.

i.e. differentiating between 303-141-100A-01 and 303-141-100A-03

Maybe even more useful would be knowing what other text is in the reference cell that you are excluding from the search. If the PN is separated from this text by a space you could use a formula like:

=IF(OR((VLOOKUP(LEFT(C13,FIND(" ",C13,1)-1) & "*",A:H,3,FALSE))="N/A",(VLOOKUP(LEFT(C13,FIND(" ",C13,1)-1) & "*",A:H,3,FALSE)="x")),0,VLOOKUP(LEFT(C13,FIND(" ",C13,1)-1) & "*",A:H,3,FALSE))

 

[lucky07]

The problem is when I start to look at longer PN's with similar starting numbers. Basically different revisions of the same thing. I am dealing with varying character lengths up to 15 characters.

I am unsure of how the find in the Left() function would work with all lengths.

 

[borgem]

If there is a space at the end of the PN, then the =FIND(find_text,within_text, [start_num]) locates the first space " " and returns the position of the space (hence the -1 after). If the PN's have spaces in them or do not have a space at the end, there will have to be a different approach.

 

[lucky07]

I understand. There are no spaces at the end of the PN's.

 

[borgem]

Gotcha. What else is in the cell with the PN then that requires that you use the left() function?

 

[lucky07]

There isn't anything else in the cell. It just happened to be a function that was inside of a partial solution to my problem so I stuck with it without checking to see how necessary it was. It seemed to be a way to search using a particular length.

There is probably a better way to do that now that I think about it.

 

[borgem]

If there is nothing else and you just want to match the PNs exactly every time, you can just use the vlookup w/o left(). Without knowing more, maybe one of these variations will solve your problem. The first one will take the closest match if an exact isn't available. Regardless of order, it will try to find the exact match first.

=IF(OR(VLOOKUP(C13,A:H,3,TRUE)="N/A",VLOOKUP(C13,A:H,3,TRUE)="x"),0,VLOOKUP(C13,A:H,3,TRUE))

OR

=IF(OR(VLOOKUP(C13,A:H,3,FALSE)="N/A",VLOOKUP(C13,A:H,3,FALSE)="x"),0,IF(VLOOKUP(C13,A:H,3,FALSE)="",VLOOKUP(LEFT(C13,LEN(C13)-1),A:H,3,FALSE),VLOOKUP(C13,A:H,3,FALSE))