Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Nov 2013
    Posts
    40
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Vlookup & Subsitute to get result

    Hello,
    I am trying to vlookup the value e.g. 005 from a table but the column that i want to vlookup will have the format 800021005.
    There are 9 fields but i want to have only the last 3 fields, Subsitute the first 6 digits.

    How to combine VLOOKUP&SUBSTITUTE to achive the outcome.

    Please refer to my sample workbook for more details.
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Are you looking up the invoice and wanting to put the last 3-digits of the ID associated with that invoice?
    If so, based on the invoice starting in B2, enter this for the ID:

    =TEXT(1000*MOD((INDEX(Sheet1!$A$1:$A$60,MATCH(B2,S heet1!$B$1:$B$60,0))/1000),1),"000")

    And, fill that down.

    If you don't want the ID as text, you could eliminate that portion and apply a custom format.

    If you don't need to do the look-up and just want to adjust the ID to the last three digits, on Sheet 1 try:

    =1000*MOD(A2/1000,1) and apply custom formatting or TEXT as above.
    Last edited by kweaver; 2013-11-01 at 16:58.

  3. #3
    Lounger
    Join Date
    Nov 2013
    Posts
    40
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kweaver View Post
    Are you looking up the invoice and wanting to put the last 3-digits of the ID associated with that invoice?
    If so, based on the invoice starting in B2, enter this for the ID:

    =TEXT(1000*MOD((INDEX(Sheet1!$A$1:$A$60,MATCH(B2,S heet1!$B$1:$B$60,0))/1000),1),"000")

    And, fill that down.
    Thanks Kweaver for the response, the formula is perfect, 1 more request i want to convert the formula to macro which will fill down the column A with ONLY VALUE.
    It means it will not have formula in the column A visible only the agrument value will be displayed.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about:
    Code:
    Option Explicit
    Sub FillColA()
      Dim lLastRow As Long
      With Worksheets("Sheet2") 
        lLastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        With .Range("A2:A" & lLastRow)
          .Formula = "=TEXT(1000*MOD((INDEX(Sheet1!$A$1:$A$60,MATCH(B2,Sheet1!$B$1:$B$60,0))/1000),1),""000"")"
          .Copy
        End With
        .Range("A2").PasteSpecial (xlPasteValues)
      End With
    End Sub
    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    carmine (2013-11-07)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •