Finding Max value from a set of data from partial match entered in reference cell

[saculim]

Hi I have problem in combining functions in excel. I am trying to find the Max value from set of data based on a partial match from a reference cell. For example:

My set of data is:
ColA ColB
1001 1
1002 2
1003 6
1001 3
1004 8
1003 9
1005 4
2001 3
2002 7
2003 10
2002 2
2004 15
2005 9
2004 18

Case 1 reference cell is 10
Case 2 reference cell is 20

For Case 1 i want the value returned to be 9 as it is the highest value based on the partial match in ColA containing a "10"
and For Case 2 i want the value returned to be 18 as it is the highest value based on the partial match in ColA containing a "20"

I am lost in solving this problem. I have used MAX(IF(A1:A14=1001,B1:B14)) then I press shift+ctrl+ enter to make it an array but that is based off a exact match of what i would enter my reference cell. I suspect in using wildcards in excel but I am lost in utilising it.

Please help solve it.

Many Thanks

 

[inactiveuserps07]

@saculim, for ColA contains "10" (assuming Column A is formatted as numbers), use this formula with Ctrl+Shift+Enter:

=MAX(IF(ROUND(A1:A14/100,0)=10,B1:B14))

For ColA contains "20," just replace the 10 with a 20.

 

[saculim]

Thank you very much That helped me solve my problem.x

 

[inactiveuserps07]

Glad to hear it. Thanks for reporting back.