I agree with Jim Thomlinson; things could get pretty icky. If there was a
small limit to the number of potential duplicate entries, you might code up a
solution, but if the potential is beyond a few (guessing at 5 or 6 as a
practical limit), then the formula would probably be too long/complex to deal
with unless we go with some slick variation of SUMPRODUCT(). And I hadn't
even thought about that one yet.
What I have come up with is a hybrid solution, combining both Excel
worksheet functions COUNTIF() and VLOOKUP() along with a user defined
function (UDF) to deal with the times there are multiple matches.
Rules for my example: Your EXP table occupies range $E$2 over & down to
$J$7. Obviously it's probably larger than that, but keep my limit in mind
when looking at the example formula:
=IF(COUNTIF($E$2:$E$7,$A$2)=1,VLOOKUP($A2,EXP,6,FALSE),
getlargestofmultiples($A$2,$E$2:$J$7,6))
Remember that would be all one line in an Excel cell formula.
What that says is: if there's only one match to whatever is in A2 in the
first column of the lookup table, EXP ($E$2:$J$7, remember) then stick with
the fast VLOOKUP() formula, but if the count is either 0 or greater than 1,
then use the UDF. Actually we could use a second COUNTIF() to keep from
running the UDF uselessly when COUNTIF() returned zero, but we'll write that
later. Now, here is the code for the UDF. To put it into use, press
[ALT]+[F11] to open the VB editor, then use Insert --> Module to open a new
code module, and copy the code below into the module and then close the VB
editor.
Function GetLargestOfMultiples(seekValue As Range, _
searchList As Range, returnColumn As Integer) As Variant
'Returns largest value associated with multiple entries
'in a table. Is not case sensitive.
'parameters are similar to, but NOT exactly like those
'of a VLOOKUP() formula
' INPUT PARAMETERS:
' seekValue = address of value to match in the table
' searchList = address of single column of entries to
' find matches in. Much as 1st column of
' a VLOOKUP table
' returnColumn = integer of column relative to the
' searchList, INCLUDING the searchList
' column, so it's just like the
' return column value in a VLOOKUP()
'
'
Dim anyEntry As Range ' to look through searchList
' better to type foundValue as same as
' you anticipate returning such as
' integer, long, currency, etc if able
' Does currently assume some numeric value to be returned
Dim foundValue As Variant
'set up a default/failed return value
GetLargestOfMultiples = "No Match Found"
foundValue = 9E-99 ' make it a very small number
'make it foundValue="" if you are returning text values
For Each anyEntry In searchList
If UCase(Trim(anyEntry)) = UCase(Trim(seekValue)) Then
'we found a match
If anyEntry.Offset(0, returnColumn - 1) _
foundValue = anyEntry.Offset(0, returnColumn - 1)
End If
End If
Next
If foundValue <> 9E-99 Then ' compare against original
GetLargestOfMultiples = foundValue
End If
End Function
That's it. Since our original formula would cause this routine to be run
when the value in A2 doesn't even exist in the list, we might want to skip
running a potentially long process (the UDF) when there's no hope of
returning a value, so
we could modify the original formula to this
=IF(COUNTIF($E$2:$E$7,$A$2)=1,VLOOKUP($A$2,EXP,6,FALSE),IF(COUNTIF($E$2:$E$2,$A2)>0,getlargestofmultiples($A$2,$E$2:$J$7,6),"not in list"))
20,