combining INDEX & MATCH with ISNUMBER?

[steveh1873]

Dear All,

I would like to look in column C and return a value from column B into column D by looking up column A (but, the returned value from column B needs to be the greatest value in the lookup that is relevant to that cell.

i.e.
Column C shows the cell "Steve, John, Callum". Via the lookup of columns A and B you can see the following:


Steve = 23, John = 25, Callum = 12,
therefore 25 is returned.


[TABLE="width: 266"]
<tbody>[TR]
[TD][TABLE="width: 330"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Age[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Names[/TD]
[TD="align: center"]Age[/TD]
[/TR]
[TR]
[TD="align: center"]John[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"][/TD]
[TD="align: center"]John, Samuel[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]Jim[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Jim, James, Ben[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]James[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Ben[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]Steve[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Steve, John, Callum[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]Samuel[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Chris[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD="align: center"]Matt[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Tom[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]Mike[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Timothy, Callum, Matt[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]Mark[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Ben[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Chris[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Callum[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Tom[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Timothy[/TD]
[TD="align: center"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help is greatly appreciated.

I believe a combination of index, match, isnumber and search in an array formula is the answer but just can't seem to piece it together

Regards,

 

[Comfy]

Here's my attempt:

Excel 2010
	A	B	C	D	E
1	Name	Age		Names	Age
2	John	25		John, Samuel	25
3	Jim	25		Jim, James, Ben	25
4	James	25		Ben	15
5	Steve	23		Steve, John, Callum	25
6	Samuel	23		Chris	12
7	Matt	20		Tom	10
8	Mike	20		Timothy, Callum, Matt	20
9	Mark	20
10	Ben	15
11	Chris	12
12	Callum	12
13	Tom	10
14	Timothy	10
Sheet1

Cell Formulas
Range	Formula
E2	{=MAX(IF(IFERROR(FIND($A$2:$A$14,D2),0)>0,$B$2:$B$14,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[krykm]

Alternatively,

=MAX(IF(ISNUMBER(FIND($A$2:$A$14,D2)),$B$2:$B$14)) Ctrl+Shift+Enter

 

[steveh1873]

What an absolute beaut!

Very different to how I was approaching it. Thank you ever so much.

Regards,

Steve

 

[steveh1873]

Thank you also, this works with equal precision.

Regards,

Steve