+ Reply to Thread
Results 1 to 12 of 12

complicated look up with spaces in search table

  1. #1
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    47

    complicated look up with spaces in search table

    Hello,

    Please find attached file

    I am trying to pull out desired values from look table that is not in order

    With altering the look up table table, can we pull out values that match single criteria

    I have shown desired results for easiness

    regards
    Attached Files Attached Files

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: complicated look up with spaces in search table

    Try below formulas, copy and paste towards down
    c3=LOOKUP(2,1/SEARCH("*-*-*"&$B3&"*",$P$4:$P$34),LOOKUP(ROW($Q$4:$Q$34),ROW($Q$4:$Q$34)/($Q$4:$Q$34=""),$O$4:$O$34))
    d3=LOOKUP(2,1/SEARCH("*-*-*"&$B3&"*",$P$4:$P$34),LOOKUP(ROW($Q$4:$Q$34),ROW($Q$4:$Q$34)/($Q$4:$Q$34=""),$P$4:$P$34))
    e3=LOOKUP(2,1/SEARCH("*-*-*"&$B3&"*",$P$4:$P$34),$T$4:$T$34)
    f3=LOOKUP(2,1/SEARCH("*-*-*"&$B3&"*",$P$4:$P$34),$S$4:$S$34)
    g3=LOOKUP(2,1/SEARCH("*-*-*"&$B3&"*",$P$4:$P$34),LOOKUP(ROW($Q$4:$Q$34),ROW($Q$4:$Q$34)/($Q$4:$Q$34=""),$R$4:$R$34))
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: complicated look up with spaces in search table

    thanks heaps samba ravi. this is what I needed. i'll get back and learn how you did this

  4. #4
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: complicated look up with spaces in search table

    Hi Sambal ravi, Can I ask why did the formula not work in the example given in "issue" tab of file attached ?

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: complicated look up with spaces in search table

    Hi, as per your update info stated in #4, what is the final format of lookup table?
    This below solution works for both versions, with the same column R contains text in rows those help to define the full name in column O
    Employ a helper column N, to establish the full names those have been merged
    N4 drag down:
    Please Login or Register  to view this content.
    Then in C3:
    Please Login or Register  to view this content.
    Array fomula must be cofirmed by Ctrl-shift-enter

    See attachment for the rest.
    Attached Files Attached Files
    Quang PT

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: complicated look up with spaces in search table

    Please try at E3

    =TEXT(LOOKUP(9,SEARCH("-*"&$B3,$P$4:$P$34),$T$4:$T$34),";;-;@")
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: complicated look up with spaces in search table

    thanks heaps bebo and Bo-Ry...both solutions worked perfect. greatly appreciate your valuable time and effort

  8. #8
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: complicated look up with spaces in search table

    i'm not able to mark this thread solved. I normally can see the drop down on right side...but cant see this anymore. not sure if settings have been changed. if admin can pls mark this thread solved

  9. #9
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: complicated look up with spaces in search table

    Hi, I have bene trying to locate the error in my lookup formula, but not getting anywhere. Please assist

    See attached file
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: complicated look up with spaces in search table

    just a bump to the post ...to see if anyone can help

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: complicated look up with spaces in search table

    Please try at
    B3
    =LOOKUP(MATCH(0,-SEARCH(TEXT(A3,"-\*"&REPT(0,6)),'TR -2 latest'!$C$1:$C$28)),ROW('TR -2 latest'!$E$1:$E$28)/(('TR -2 latest'!$E$1:$E$28>0)),'TR -2 latest'!$C$1:$C$28)

    C3
    =INDEX('TR -2 latest'!$B$1:$B$28,MATCH(B3,'TR -2 latest'!$C$1:$C$28,))
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    01-11-2012
    Location
    sweden
    MS-Off Ver
    Excel 2007
    Posts
    47

    Re: complicated look up with spaces in search table

    thank you bo, I will try to understand the mechanics of your formula. it seems it is not working for my larger file with other values

+ 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. Complicated Search - Best Approach?
    By kjurcz in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-07-2017, 10:05 AM
  2. [SOLVED] Revise formula to remove sapces from text in another cell
    By topaz99 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-01-2015, 01:30 PM
  3. Complicated Cross Spreadsheet Search
    By rbac in forum Excel General
    Replies: 6
    Last Post: 04-05-2013, 05:06 PM
  4. Need Help with a complicated Search/ IF formula
    By RFitzgerald in forum Excel General
    Replies: 3
    Last Post: 05-26-2012, 01:02 PM
  5. Complicated Search and Verify
    By SymphonyTomorrow in forum Excel General
    Replies: 0
    Last Post: 12-14-2010, 10:21 PM
  6. Complicated Search Macro
    By wilro85 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-22-2007, 11:30 AM
  7. Help with a complicated search function in Excel
    By Ironman273 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-09-2006, 12:15 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