VLOOKUP - search only last digits?

[Cat3TRD]

Hi,

I'm trying to have VLOOKUP only search the last digits of a number.

The number I'm searching for is 10 digits long, and the numbers I'm searching through are 11 or greater. The last 10 digits will always match, but the first digits are not always relevant.

Is this possible?

Example:

Sheet 1
A1 has 1234567890 (10 digits)
B1 I want to have the 5 digit number from Sheet 2

Sheet 2
A1 has 01234567890
B1 has 12345


Because of that "0" at the beginning (which can be any number at all, just using "0" as an example) VLOOKUP won't return anything.

Thanks!

 

[jeffreybrown]

What if you convert 01234567890 into a number, the 0 will drop off?

 

[Cat3TRD]

Well, it's not always a zero - sometimes it's a 3 or a 6 or whatever number. Also, the first relevant digit can be a zero.

BTW - I'm in San Antonio too - 'bout time we got some rain.

 

[leigao84]

The easiest way is just to make another column with the last 10 digits =LEFT(A1, 10) and then lookup using this new column.

 

[jeffreybrown]

Yes the rain was nice.

Your data is not setup for a Vlookup. Vlookup's look for a value in the farthest leftmost column and then return a value to the right.

Is there more to your data? Is this the actual data, just one value?

Look at my signature block and you can post a small sample of your data so we can get you the most appropriate solution.

 

[shg]

You could do it like this ...

       -----B----- --C-- ----D----- --E--
   2     bigNum    Data    lilNum   Match
   3   16873228390 $C$3  2281700412 $C$9 
   4   95696165706 $C$4                  
   5   79136536309 $C$5                  
   6   93054439707 $C$6                  
   7   87185190234 $C$7                  
   8   88595471200 $C$8                  
   9   12281700412 $C$9                  
  10   43757341180 $C$10                 
  11   14089703426 $C$11                 
  12   45988538838 $C$12                 
  13   65757377238 $C$13                 
  14   45035651926 $C$14                 
  15   49469677219 $C$15                 
  16   33394851797 $C$16                 
  17   57732978958 $C$17

The formula in E3 is

=INDEX($C$3:$C$17, MATCH(D3 & "", RIGHT($B$3:$B$17, 10), 0))

... confirmed with Ctrl+Shift+Enter.


It's a very expensive formula, though, and if you needed a lot of them, there are better ways.

 

[Cat3TRD]

While waiting for a reply - I decided to go ahead and do something like what leigao84 suggested. In my database file, I just added another column all the way to the left, with the numbers in column B shortened to 10 digits.

I think this is the easiest for everyone involved. The only question this brings up is whether or not there's a way to speed up the process of applying a formula to an entire column without actually dragging it down? With tens of thousands of rows to drag it over, it can take quite a while just waiting for it to scroll to the end...

Thank you all for your help

 

[crook_101]

Hi,

Easiest way instread of dragging a massive 10K+ range is to Control & C to copy the formula, then highlight the range where you want it pasting and then control & V.

If it is an expensive formula at least you can leave your machine fo r5 mins to grab a beer

HTH
Ian

 

[jeffreybrown]

So column B is the column which will have the formula to shorten the formula?

Beside double clicking the fill handle and sending the formula down to the end of the length of column A you would need to use a macro.

Is this what you want to do?

 

[crook_101]

Sorry misunderstood post - Jeffrey's post highlights the quickest way - thought OP was having problems with freezing screen.......DOH - i should just go to bed tonight