I have seen this else where, but I am having difficultly translating it to my formula. I need to modify my formula to first do a vlookup, then when it finds more than one matching cell in the row to figure out which has the maxium value and pick that cell to return in the vlookup match result.

My real issue is not that I need a max result but to ignore matches where the corresponding data to return is either blank or zero. But I figured integrating the Max function with vlookup was the best solution.

Here is my current vlookup formula that needs to be modified:

=(IF(ISERROR(VLOOKUP($Q2,Bank_SL_EQ,5,FALSE)),"",VLOOKUP($Q2,Bank_SL_EQ,5,FALSE)))

You'll need a VBA function to find the instance that (either first has data, or the last instance that has data):

Open in new window

Usage:

=maxvlookup(lookup_value,t

e.g.,

=maxvlookup($E4,$A$2:$B$10

=maxvlookup($E4,$A$2:$B$10

See example workbook attached.

Dave

maxVlookup-r1.xls