+ Reply to Thread
Results 1 to 6 of 6

Lookup question matching part of lookup value in the table array

  1. #1
    Registered User
    Join Date
    09-05-2013
    Location
    ireland
    MS-Off Ver
    Excel 2007
    Posts
    5

    Lookup question matching part of lookup value in the table array

    Hi All,
    I'm new here, so i will try to be as clear as possible:
    I have a column which contains a full address i.e.
    285 Charlemont, Griffith Avenue, Dublin 9
    262 Larkhill Road, Whitehall, Dublin 9

    I have a lookup table that contains
    (A1)Estate (A2) delivery day
    (B1)Charlemont (B2)Tuesday
    (C1)Larkhill Road (B3)Wednesday

    How can I add a new column beside the full address that will contain the relevant delivery day?

    Please note that changing text to column and doing a vlookup doesn't work because some estates have 2 or more words.

    Thanks for help in advance

    K
    Last edited by kosmo; 09-05-2013 at 09:33 AM. Reason: misspelling

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Lookup question matching part of lookup value in the table array

    welcome to the forum, kosmo. do upload a sample excel file next time so that we do not have to create a file for testing. assuming your full addresses are in H1, try this:
    =IFERROR(LOOKUP(2^15,SEARCH($B$1:$C$1,H1),$B$2:$C$2),"")
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    09-05-2013
    Location
    ireland
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Lookup question matching part of lookup value in the table array

    Hi Benishiryo

    thank you for your help.

    my data is organised a bit different (lookup table is in columns) not sure if this make any difference - i'll upload a sample dataDAta SAmple.xlsx

    tried it on my data, and it doesn't work...

    K

  4. #4
    Registered User
    Join Date
    09-05-2013
    Location
    ireland
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Lookup question matching part of lookup value in the table array

    Actually it worked,

    my mistake i selected the entire refence columns B:B as sopposed to specific cells B1:B400

    Thanks a mill for your help.

    can i ask what does the 2^15 stands for

    K

  5. #5
    Registered User
    Join Date
    09-05-2013
    Location
    ireland
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Lookup question matching part of lookup value in the table array

    Actually it worked,

    my mistake i selected the entire refence columns B:B as sopposed to specific cells B1:B400

    Thanks a mill for your help.

    can i ask what does the 2^15 stands for

    K

  6. #6
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Lookup question matching part of lookup value in the table array

    glad to help. 2^15 is 2 to the power of 15 & that equates to 32,768. xlsx files allow up to 32,767 characters in a cell. so that's supposed to be a number big enough to ensure i'm able to pick up whatever results the SEARCH formula returns.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula Question - Value Lookup - Part 2
    By Girish Punjabi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-22-2013, 12:50 AM
  2. Advanced lookup matching part of a number
    By MayfairBlue in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 08-16-2012, 08:30 AM
  3. Lookup on Part of Lookup Array
    By franciz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-21-2009, 08:32 AM
  4. Paging daddylongleggs - lookup question part 2
    By no dice in forum Excel General
    Replies: 3
    Last Post: 08-20-2007, 07:41 PM
  5. [SOLVED] LOOKUP question-s it possible to have LOOKUP do a calculation in the array?
    By Bob S in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-12-2006, 09:30 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1