Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello

    I'd like to vlookup from the attached sheet but the number i'm after is inside another number.

    I've attached the sheet. Column B: has these numbers; 26800123330 I'd like to omit the first 5 and the last one and search on 012333.

    Is that possible?

    Many thanks

    Rob
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Do you want to look up 012333 in a table on another sheet, or do you want to search for 012333 in column B in this sheet?

  3. #3
    New Lounger
    Join Date
    May 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='768421' date='31-Mar-2009 09:30']Do you want to look up 012333 in a table on another sheet, or do you want to search for 012333 in column B in this sheet?[/quote]


    thanks Hans

    I'd like to lookup from another sheet

    rob

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Instead of VLOOKUP, you can use a combination of INDEX and MATCH in an array formula (i.e. confirm the formula with Ctrl+Shift+Enter instead of just Enter).
    Let's say the value that you want to look up is in cell A2 on the active sheet. You want to match this value to the 6 characters starting at position 5 in column B of Test sheet. And you want to return the corresponding value from column G on Test sheet.

    =INDEX('Test sheet'!$G$6:$G$417,MATCH(A2,MID('Test sheet'!$B$6:$B$417,5,6),0))

    Don't forget to confirm with Ctrl+Shift+Enter!

Posting Permissions

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