Compare 3 columns, match rows based on other 2 colums

[Dustinkli]

I have a formula that looks at a cell in a column and then cross references another column and then pulls the value of the cell to the right of that 3rd column. However, I would like it to search based on if a value exists in that column anywhere in the cell rather than requiring the entire cell to match.

Example:

Name	Age Lookup	Name	Age
Bob		John	22​
John		Tina	31​
Tina		Claudia	51​
Albert		Bob	34​
Claudia		Albert	63​

The formula I have I would put in the age lookup column and would be: =INDEX($D$2:$D$10000,MATCH(A2,$C$2:$C$10000,0))
that would return 34.

However, what I am trying to do is set it so that it doesn't require the entire cell to look it up. For instance if it is like this:

Name	Age Lookup	Name	Age
Bob		John	22 years
John		Tina	31​
Tina		Claudia	51 years
Albert		Bob	34 years old
Claudia		Albert	age 63

I want the formula to be able to look at the "Age" column and pull the match if it exists ANYWHERE in the cell so it should do the lookup regardless.

 

[Fluff]

I'm not sure what you are after as your description makes no sense.
You are comparing col A with col C & returning what is in col D, therefore it makes no difference if col D is 34, 34 Years old, nearly 34.

 

[Dustinkli]

I'm not sure what you are after as your description makes no sense.
You are comparing col A with col C & returning what is in col D, therefore it makes no difference if col D is 34, 34 Years old, nearly 34.

My fault. I made a mistake in the way I set it up. It would be like this:

Name	Age Lookup (formula goes in this column)	Name	Age
Bob	34	John Test	22
John	22	Tina Tester	31
Tina	31	Claudia Lname	51
Albert	63	Bob Smith	34
Claudia	51	Albert Johnson	63

 

[Fluff]

In that case try

=INDEX($D$2:$D$10000,MATCH(A2&"*",$C$2:$C$10000,0))