Find the last entry of a part of text and return an adjacent cell.

[selma]

Hi!
I am finding hard to make a formula to find the last entry of a part of a text and return the content of an adjacent cell.
What I want is to create a file that can control some contracts and check the last extension. The same extension would refer to many contracts at the same time.

Here an example of a table:
A B C
<TABLE style="WIDTH: 323pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=430><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 112pt; mso-width-source: userset; mso-width-alt: 5449" width=149><COL style="WIDTH: 163pt; mso-width-source: userset; mso-width-alt: 7936" width=217><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64>Index</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 112pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=149>Type</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 163pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=217>Description</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>1.01</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>Project</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>Name of the project</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>1.02</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>Project Extension</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>Extension of project 1.01</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>1.03</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>Project</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>Name of project</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>1.04</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>Project</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>Name of project</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>1.05</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>Project</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>Name of project</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>1.06</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>Project Extension</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>Extensions of projects 1.01 and 1.05</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>1.07</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>Project Extension</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>Extension of projects 1.01, 1.04 and 1.05</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>1.08</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>Project Extension</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>Extension of projects 1.04 and 1.05</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20>1.09</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>Project Extension</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>Extension of projects 1.03 and 1.05</TD></TR></TBODY></TABLE>

I tried with INDEX and MATCH but didn't worked well.
I want that the collumn D shows the index number to the last extension to that project.
Ex:
D2 will return 1.07
D4 will return 1.09
D5 will return 1.08
D6 will return 1.09

It doesnt matter if I will have to create more collumns. But I prefer not to use VBA (I don´t know how to do it - sorry).
Any suggestions are wellcome!

All the best

Selma

 

[Mike Blackman]

Hi,

How about this;

=INDEX(B:B,MATCH(9.99999999999999E+307,A:A))

 

[selma]

Dear Mike,

Thank you for your prompt answer! Was really nice from you.
Sorry, but the formula didn´t work well.
I tried, but it didn't gave me the expected result.
I only got #NAs.

I think one of the reasons for this is that A2 (1.01) work as a text (excel here uses "," for decimals and a number with "." will be treated as a text).
So the formula was not able to find the text 1.01 (and so on) inside the cells in column C.
Even when I converted the text "1.01" to number "1,01" it was not able to find the number inside the tex in collumn C.

But I was not clear in my request, so I am the only to blame.

Well, what I need is a formula that can search the text that are in collumn A ,inside the cells in collumn C , and return the contents of collumn A in the correspondent line.

Example: If the formula (in D5) would be able to find the LAST occurence of contents of A5 (1.04) inside somewhere in collumn C (wich is in C9) so D5 will return A9 ("1.08")

Collumn B is just to control that the item is a project or the extension of the project. So you can forget about it.

What do you think? Is that too weird?
I am wide open to suggestions so I can make this work.

Thank you! Have a nice week!

Selma

 

[Richard Schollar]

Hi Selma

Here's one possibility

Excel Workbook
	A	B	C	D
1	Index	Type	Description	Last Named Project
2	1.01	Project	Name of the project	1.07
3	1.02	Project Extension	Extension of project 1.01
4	1.03	Project	Name of project	1.09
5	1.04	Project	Name of project	1.08
6	1.05	Project	Name of project	1.09
7	1.06	Project Extension	Extensions of projects 1.01 and 1.05
8	1.07	Project Extension	Extension of projects 1.01, 1.04 and 1.05
9	1.08	Project Extension	Extension of projects 1.04 and 1.05
10	1.09	Project Extension	Extension of projects 1.03 and 1.05
Sheet1

Excel 2010

Cell Formulas
Range	Formula
D2	=IF(COUNTIF($C$2:$C$10,"*"&A2&"*")=0,"",LOOKUP(9.9+307,SEARCH(A2,$C$2:$C$10),$A$2:$A$10))

 

[selma]

Richard,

It worked perfectly!!! Underbar!ray:

THANK YOU VERY MUCH!!!!


Selma