+ Reply to Thread
Results 1 to 5 of 5

How to 'lookup' a cell row number in a formula from a separate =MATCH formula result

  1. #1
    Registered User
    Join Date
    09-11-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    53

    How to 'lookup' a cell row number in a formula from a separate =MATCH formula result

    I have a spreadsheet searching for specific text in various cells.

    First I need to identify which row the specific text is in (using the =match function). Which I have done no problem.

    Next I need to extract just the required text. I am doing this using a series of =MID(LEFT( .. FIND to find text between two specified strings on a specified row.

    However my issue is when I do not want to specify (or hard code) the cell reference in the formula and I want the formula to somehow 'lookup' the required cell reference from the results of the separate =MATCH formula mentioned earlier.

    Attached is a spreadsheet to better explain.

    Any help much appreciated

    Matt
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: How to 'lookup' a cell row number in a formula from a separate =MATCH formula result

    unclear how long the strings can be in reality, however below might work for you in terms of single-cell:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    NOTE: replace X in above with script -- board software won't allow that syntax as presumes it's actually a script being fired!!

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

    Re: How to 'lookup' a cell row number in a formula from a separate =MATCH formula result

    Please try
    =TRIM(MID(SUBSTITUTE(SUBSTITUTE(INDEX(A1:A4,MATCH("*fox*",A1:A4,0)),"/",),"< script >",REPT(" ",99)),99,99))

    Remove space before and after script in blue above.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-11-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: How to 'lookup' a cell row number in a formula from a separate =MATCH formula result

    Hi

    Not quite there yet as my example was too simple. I've added more to the lookup area in the spreadsheet (attached). In this example, there is now more than once instance of "< script >" in the row which now causes more text to be added. I think the logic needs to change somewhere.

    See attached
    Attached Files Attached Files

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

    Re: How to 'lookup' a cell row number in a formula from a separate =MATCH formula result

    Please try
    =LOOKUP(0,-FIND("fox",MID(SUBSTITUTE(SUBSTITUTE(VLOOKUP("*fox*",A$1:A$6,1,),">","<"),"<",REPT(" ",300)),ROW(A$1:A$20)*300-299,300)),TRIM(MID(SUBSTITUTE(SUBSTITUTE(VLOOKUP("*fox*",A$1:A$6,1,),">","<"),"<",REPT(" ",300)),ROW(A$1:A$20)*300-299,300)))

+ 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. Replies: 2
    Last Post: 01-16-2018, 07:21 AM
  2. Replies: 5
    Last Post: 12-18-2015, 01:24 PM
  3. Replies: 9
    Last Post: 11-20-2014, 04:27 PM
  4. [SOLVED] Match formula brings #N/A result. How to change to blank cell?
    By JPWRana in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-24-2013, 11:40 AM
  5. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM
  6. Replies: 2
    Last Post: 01-13-2012, 05:21 PM
  7. Match/Lookup/Result Formula
    By Killer17 in forum Excel General
    Replies: 1
    Last Post: 11-03-2008, 10:28 PM

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