Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup for greatest value
How would I add logic to this formula =VLOOKUP($A2,EXP,6,FALSE) so that it
returns the largest value if I have multiple matches in the table? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup for greatest value
Vlookup does not deal well with multiple matches. Does your existing formula
return a number or Text. If it is a number we can look into using sumproduct. If not things get difficult. -- HTH... Jim Thomlinson "Shaun" wrote: How would I add logic to this formula =VLOOKUP($A2,EXP,6,FALSE) so that it returns the largest value if I have multiple matches in the table? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup for greatest value
well it is a date so in a way yes it is a number.
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup for greatest value
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,FA LSE), 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 Then 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,F ALSE),IF(COUNTIF($E$2:$E$2,$A2)0,getlargestofmult iples($A$2,$E$2:$J$7,6),"not in list")) "Shaun" wrote: How would I add logic to this formula =VLOOKUP($A2,EXP,6,FALSE) so that it returns the largest value if I have multiple matches in the table? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup for greatest value
It isn't clear what you are asking for. By largest value with multiple
matches, do you mean to return the item that is in the highest numbered row? Or do you mean the largest value from the lookup? For example, suppose you have in C4:C20 and D4:D20, 1 100 2 200 3 900 1 400 2 500 3 600 4 700 If you are looking up the value 3 in C4:C20, which instance of 3 do you want to return? The first lookup match for 3 is 900 and the last row match for 3 is 600. Which you do want? To get the value in D for the last occurrence of 3 in C, use the following array formula: =OFFSET(C$4,MAX((C4:C20=3)*(ROW(C4:C20)))-ROW(C$4),1,1,1) To get the max value in D where C = 3, use =MAX((C4:C20=3)*(D4:D20)) This is an array formula, so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }. You do not type in the braces -- Excel puts them in automatically. The formula will not work properly if you do not enter it with CTRL SHIFT ENTER. For much more information about array formulas, see http://www.cpearson.com/Excel/ArrayFormulas.aspx. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Wed, 20 Jan 2010 12:56:01 -0800, Shaun wrote: How would I add logic to this formula =VLOOKUP($A2,EXP,6,FALSE) so that it returns the largest value if I have multiple matches in the table? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup for greatest value
Using your defined name EXP, this seems to work ok
Array-enter, ie press CTRL+SHIFT+ENTER to confirm: =MAX(IF(OFFSET(EXP,,,,1)=$A2,OFFSET(EXP,,5,,1))) The "5" in the offset's col param is equivalent to the "6" (the col index) in your vlookup. Success? celebrate it, hit the YES below -- Max Singapore --- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup for greatest value
Max thanks for the poke at this but I need it to keep the reference in mind
when looking for the highest values of that reference. For example EXP table: A F 1 Bob 150 2 Jed 90 3 Tom 80 4 Bob 100 5 Jed 180 6 Tom 160 Desired look up: (so that I am only looking at the highest value of employees payout.) A F 1 Bob 160 2 Jed 180 3 Tom 160 Any help would be great! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup for greatest value
But Shaun, as-is, that is exactly what the earlier expression evaluates and
returns as the result. It should have worked ok for you. I tested it fine here before posting. Can you re-check that you have array-entered (CSE) the expression correctly?Visually look for the curly braces { } in the formula bar of the cell where you placed that earlier expression. These curlies are inserted by Excel if the CSE** is correctly done. If you see the curlies, its fine. If not, then it means that the formula was NOT array-entered, and hence it will not evaluate correctly. I know of no other way to check that a formula has been correctly array-entered. **CSE = pressing CTRL+SHIFT+ENTER to confirm the formula (instead of just pressing ENTER) Try it again. Then do it justice, re-rate the earlier response. -- Max Singapore --- "Shaun" wrote: Max thanks for the poke at this but I need it to keep the reference in mind when looking for the highest values of that reference. For example EXP table: A F 1 Bob 150 2 Jed 90 3 Tom 80 4 Bob 100 5 Jed 180 6 Tom 160 Desired look up: (so that I am only looking at the highest value of employees payout.) A F 1 Bob 160 2 Jed 180 3 Tom 160 Any help would be great! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Greatest Number | Excel Worksheet Functions | |||
greatest value function? | Excel Discussion (Misc queries) | |||
Finding the greatest value | Excel Discussion (Misc queries) | |||
Greatest to smallest | Excel Discussion (Misc queries) | |||
Displaying Greatest to Least? | Excel Discussion (Misc queries) |