# Find last column that contains given text

1. ## 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

2. ## Re: Find last column that contains given text

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

B3:
Formula:
`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:
`Please Login or Register  to view this content.`

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?

4. ## 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. ## 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.``

6. ## 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!

7. ## Re: Find last column that contains given text

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

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

#### 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