+ Reply to Thread
Results 1 to 7 of 7

Find last column that contains given text

  1. #1
    Registered User
    Join Date
    11-24-2020
    Location
    US
    MS-Off Ver
    365
    Posts
    3

    Find last column that contains given text

    So I'm trying to find the number of days between dates but based off data in another row. For example, in the below image, I want the 3rd row to auto populate based on what date and letter I enter in the first 2 rows.
    The number of days calculated is from the date in the same column, to the last date of the same letter in row 2.
    So, cell D3=D1-A1, E3=E1-B1, F3=F1-D1, and G3=G1-C1.
    I figured it'd be a good starting point to get the last column that contains the same text, but can't seem to get this figured out.
    Any help is much appreciated. Thanks!


    Screenshot 2020-11-24 150509.png
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,138

    Re: Find last column that contains given text

    If you really are using Office 365, you should have XLOOKUP, so try

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

    Fill B3 right as far as needed.

    ADDED: if the - for no previous match are 0s formatted to appear as dashes, then

    B3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by hrlngrv; 11-24-2020 at 06:38 PM. Reason: addendum

  3. #3
    Registered User
    Join Date
    11-24-2020
    Location
    US
    MS-Off Ver
    365
    Posts
    3

    Re: Find last column that contains given text

    Hmm, not sure I follow this.

    I think I can do the rest with Index, so to make this easier all I need is the last column number that contains the given text.

    For example, given the above image, how can I search all of row 2 for the last "A", which would be column 7?
    Last edited by Rikead; 11-24-2020 at 07:35 PM.

  4. #4
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,138

    Re: Find last column that contains given text

    Here's a link to an example of using XLOOKUP on OneDrive. It shows 2 variations using XLOOKUP and 2 variations using INDEX.

    Could you use INDEX? Yes, but you'd need to use it with MATCH or XMATCH or an array formula construct.

    In your original example above, G2 shows A. The previous instance of A is in cell E2. XLOOKUP(G2,$A2:F2,$A1:F1,,,-1) looks for the value of G2 in A2:F2 from right to left (that's what the -1 as 6th argument specifies) and returns the corresponding value in A1:F1, which would be the date corresponding to the previous instance of A in row 2, so the value of E1.

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

    Re: Find last column that contains given text

    Date in row 1 are text, try to input real date then format as "ddd, mmm dd, yy"

    Then in B3 and copy accross:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-24-2020
    Location
    US
    MS-Off Ver
    365
    Posts
    3

    Re: Find last column that contains given text

    Hey guys, thanks for all the feedback! I actually ended up going about it a different way using the LOOKUP(2,1/(exp,range) technique that worked perfectly. I appreciate the help though
    Exact formula starting at E3 is =E1-(LOOKUP(2,1/(B2:D2=E2),B1:D1)). Cheers!
    Last edited by Rikead; 11-25-2020 at 03:05 AM.

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

    Re: Find last column that contains given text

    Good luck!
    Could you share your solution for whom it may interested in?

+ 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. [SOLVED] Find specific text within text and result dependent text in specified column
    By Eftychia in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-08-2019, 08:39 AM
  2. [SOLVED] VBA Macro to find text string in one column and replace specific text in another column if
    By bmahfood in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-21-2017, 11:15 AM
  3. [SOLVED] How to find text then select RANGE form the text found to lastrow of Column J
    By tuongtu3 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2014, 02:45 AM
  4. [SOLVED] Search through columns and find specific text and replace that text with header column
    By adamzee in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-09-2014, 11:59 PM
  5. VBA Code to Find Specified text within a Column and perform Text to Column
    By MHamid in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-26-2014, 06:42 AM
  6. [SOLVED] Find Specific Text in Text String and Return Value in Adjacent Column
    By watchouse in forum Excel General
    Replies: 2
    Last Post: 07-11-2012, 03:53 PM
  7. Replies: 1
    Last Post: 09-01-2011, 12:05 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