Retrieve the Text which may have prefix/suffix in the database & retrieve the highestvalue

1. I have a database in one sheet with Two Rows - A Row - 1.Area Name (Text) ; B Row - 2.Area Value (Number)
2. Input values in One Sheet - A Row - Text


We need the retrieve the Text that may contain any prefix/suffix like north, south etc. & retrieve the highest value of it.

I know it is something complicated for me & don't know whether we could do this excel or not but this is my requirement.

I explained in detailed in the attached excel.

Hi Sivashanmugam,
maybe UDF

Please Login or Register  to view this content.

Nilem, Thanks a lot it serves my purpose.

I have one more query.. i want to have List of matching records (Contains - List of records that contains india)
i can provide an separate input in 1 cell, if required & get these matching results for a verification purpose.