VLOOKUP - search only last digits?

jeffreybrown

Well-known Member

Joined

Jul 28, 2004

Messages

5,152

• May 13, 2011

• #2

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

 

Upvote 0

C

Cat3TRD

New Member

Joined

Apr 24, 2011

Messages

7

• May 13, 2011

• #3

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.

 

Upvote 0

L

leigao84

New Member

Joined

Feb 22, 2011

Messages

19

• May 13, 2011

• #4

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

 

Upvote 0

jeffreybrown

Well-known Member

Joined

Jul 28, 2004

Messages

5,152

• May 13, 2011

• #5

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.

 

Upvote 0

shg

MrExcel MVP

Joined

May 7, 2008

Messages

21,836

Office Version

1. 2010

Platform

1. Windows

• May 13, 2011

• #6

You could do it like this ...

Code:

       -----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.

 

Upvote 0

C

Cat3TRD

New Member

Joined

Apr 24, 2011

Messages

7

• May 13, 2011

• #7

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

 

Upvote 0

crook_101

Well-known Member

Joined

Oct 20, 2008

Messages

682

• May 13, 2011

• #8

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

 

Upvote 0

jeffreybrown

Well-known Member

Joined

Jul 28, 2004

Messages

5,152

• May 13, 2011

• #9

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?

 

Upvote 0

crook_101

Well-known Member

Joined

Oct 20, 2008

Messages

682

• May 13, 2011

• #10

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

 

Upvote 0