vlookup problem

[Sinbad]

Hi all,

is there any way to search for a partial match of a number in a string ?

Let me explain..

We have a bunch of serial numbers and need to find any portion of it.

example:a complete serial number is

T1147004504040645

<colgroup><col width="152"></colgroup><tbody>
</tbody>

now we would search for the last few numbers i.e. 040645 (this number is in O3), it then goes to sheet 5 (called Tab5) and searches in range A1:G32919 and returns the corresponding value from Gxx

the formula I currently use is as follows =VLOOKUP(O3;Tab5!A$1:G32919;7;True)

so far so good, but it return all sorts of stuff except the correct values. Is it possible o have it display "not found" if it can not find a match ?

Thanks

 

[Firefly2012]

So what values would be in the Tab5!A column? Would it be 040645 or a composite value lke TA345604064599?

 

[Fatabuna]

=SUM(INDEX('Tab5'!$G:$G,MATCH(RIGHT($O$3,6),RIGHT('Tab5'!$H:$H,6),0)))

entered with a Ctrl+Shift+Enter.

I'm assuming that you're looking for the last 6 digits of the serial number and it's a unique value.

 

[Sinbad]

Hi, the complete mumber would be in Tab5 ie T1147004504040645
and yes, we are looking for a match on the last 6 numbers. I´ll try it in a moment. Thank you.

 

[Firefly2012]

Hi

Change your VLOOKUP to:

=VLOOKUP("*" & O3;Tab5!A$1:G32919;7;False)