Solved

vlookup then take Max result

Posted on 2012-03-14
18
515 Views
Last Modified: 2013-01-16
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)))
0
Comment
Question by:IO_Dork
  • 7
  • 6
  • 4
18 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37722057
VLOOKUP will only find one match and it will be the first instance.

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

Option Explicit

Function maxVLookup(lookup_value As Range, table_array As Range, col_index_num As Integer, Optional bFirstNonZeroBlank As Boolean = True) As Variant
Dim rFind As Range
Dim firstAddress As String
Dim foundValue As Variant
Dim lastCell As Range

    Application.Volatile
    
    Set lastCell = table_array.Offset(table_array.Rows.Count - 1, table_array.Columns.Count - 2).Resize(1, 1)

    Set rFind = table_array.Find(what:=lookup_value.Value, after:=lastCell, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)
    If Not rFind Is Nothing Then
        firstAddress = rFind.Address
        Do
            foundValue = rFind.Offset(, col_index_num - 1).Value
            If foundValue <> vbNullString And foundValue <> 0 Then
                maxVLookup = foundValue
                If bFirstNonZeroBlank Then Exit Function
            End If
            Set rFind = table_array.Find(what:=lookup_value.Value, after:=rFind, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext)
        Loop While Not rFind Is Nothing And rFind.Address <> firstAddress
        Exit Function
    End If
    
    maxVLookup = Evaluate("=NA()") 'if not found
    
    
End Function

Open in new window


Usage:
=maxvlookup(lookup_value,table_array,col_index_num,optional first=true)
e.g.,
=maxvlookup($E4,$A$2:$B$10,2,FALSE) 'finds the last nonzero/nonblank match
=maxvlookup($E4,$A$2:$B$10,2,TRUE) 'finds the first nonzero/nonblank match

See example workbook attached.

Dave
maxVlookup-r1.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37722078
PS - just change line 27 to:

maxVLookup = vbnullstring 'if not found

if you want a failed lookup to return blank.  That will save you on all the IF(ISERROR coding.

Dave
0
 

Author Comment

by:IO_Dork
ID: 37722215
Very Awesome!!  Simple, Clean, and Elegant solution.
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 37722305
I don't think you need VBA for this; an array formula should work just fine.  For example:

{=MAX(IF(INDEX(Bank_SL_EQ,,1)=Q2,INDEX(Bank_SL_EQ,,5),""))}

Do not type those curly braces, and use Ctrl+Shift+Enter instead of just Enter to finish off the formula.  Excel will then display the braces to indicate that it is an array formula.
0
 

Author Comment

by:IO_Dork
ID: 37722422
I will try the array formula as the VBA code (although nice) seems to take an extraordinarily long time to calculate (it took so long to calculate that I just end tasked Excel)...unless there is something going on with my PC right now.
0
 

Author Comment

by:IO_Dork
ID: 37722498
How do I get this to work when I have 8000 rows of data on both sheets....I have to hit shft+ctrl enter for each of the 8000 cells that I enter this formula into? if I try to do it like you normally would with an array - select all cells then hit f2 and type the formula then hit enter, all the 8000 cells with the formula result in zero...what am I missing here?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 37722549
Please see the attached sample file.

I have defined a Name, Bank_SL_EQ, as A1:E30.  Your named range is obviously much larger.

In Q2:Q6, I have various index values I want to use for the "max lookup".

To get the formulas working, I did this:

1) In R2, I entered
=MAX(IF(INDEX(Bank_SL_EQ,,1)=Q2,INDEX(Bank_SL_EQ,,5),""))
and used Ctrl+Shift+Enter

2) I copied R2

3) I select R3:R6 and hit Enter to do the paste
Q-27632466.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37722615
I would think the array function Patrick's teed up would be faster, though if your spreadsheet is large, there may still be a "wait".  If so, one other way to speed it up is to revert to the VBA approach, using VBA to post results in the cells that you can update with a button or activate tab.  In that way, there would be no formulas, just values as a result of the calc.

FYI only - The VBA version provides 1st or last instance with nonzero/nonblank results, where the array function provides the max result from any instance.

Patrick - thanks for jumping in here - a great learning for me.  Is there a way to "twist" this to find instance?  e.g., find the 2nd instance or last instance of the match?

Dave
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 41

Expert Comment

by:dlmille
ID: 37722657
if you can change your range names to look at the first column, then the column in question, the INDEX function is not needed at all...

E.g., Bank_SL_EQ_Col1 is the first column and Bank_SL_EQ_Col5 is the column to lookup:

=MAX(IF(Bank_SL_EQ_Col1=Q2,Bank+SL_EQ_Col5,""))

Ctrl+Shift+Enter to confirm

The INDEX function should be super fast, but this is the only thing I can think of that might make it faster...

Dave
0
 

Author Comment

by:IO_Dork
ID: 37723137
Thanks Patrick and Dave.

Patrick, I did what you said, just copy the first formula that I configure as an array formula and then paste it all the way down....works perfectly, Q2 changes in sequence...q3,q4,q5,q6, etc.  And it updated just as fast as a regular vlookup...plenty fast.

Although, I really should be evaluating the matches for nonzeros and nonblanks, i think this index formula should do for now...i don't expect to need to have a formula choose between lower or higher values on matches...i expect the data will either be nonblanks/nonzeros or blanks/zeros.  If i run into the former...then back to the drawingboard for me.

Dave, I like your VBA, looks like it will be good for use where I am only using it on a 1:N row lookup situation.

Thanks again all.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37723148
IO_Dork - Patrick's' formula gets the MAX from all the duplicate matches (if they are dupe's) so you'll always get the highest valued match.

Just change your data up and see what results you get - you don't have to wait on some point in the future to see if it will/won't work for you.

Cheers,

Dave
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 37723197
IO_Dork,

My formula will return a zero if in fact the max value for a given subset is zero, or if there are only blanks (or if there are no corresponding values).  To be honest, I did not really see the point of complicating the formula to test for zeroes or blanks, as the MAX function handles this implicitly.

Dave,

You asked how one could return the xth, yth, or zth value.  Here is one way, although barryhoudini probably has a more elegant way.

2nd value:
{=INDEX(INDEX(Bank_SL_EQ,,5),SMALL(IF(INDEX(Bank_SL_EQ,,1)=Q2,ROW(INDEX(Bank_SL_EQ,,1)),""),2),1)}

3rd value:
{=INDEX(INDEX(Bank_SL_EQ,,5),SMALL(IF(INDEX(Bank_SL_EQ,,1)=Q2,ROW(INDEX(Bank_SL_EQ,,1)),""),3),1)}

4th:
{=INDEX(INDEX(Bank_SL_EQ,,5),SMALL(IF(INDEX(Bank_SL_EQ,,1)=Q2,ROW(INDEX(Bank_SL_EQ,,1)),""),4),1)}

Last value:
{=INDEX(INDEX(Bank_SL_EQ,,5),LARGE(IF(INDEX(Bank_SL_EQ,,1)=Q2,ROW(INDEX(Bank_SL_EQ,,1)),""),1),1)}


Note that those interior INDEX calls can be eliminated either with structured references to Table columns, or by creating a Name to cover each column independently.

Patrick
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37723236
Patrick. Much thanks!

IO_Dork-pls req attention to award points to Patrick's teachings

Dave
0
 

Author Comment

by:IO_Dork
ID: 37723248
thanks, will  do Dave.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37723428
Patrick, I tried your instance vlookup equivalent, and it worked when the table was starting on row 1, but didn't if I moved the table.  I think the problem might be with:

ROW(INDEX(Bank_SL_EQ,,1)) <- this portion returns a rows list, but the list should start with 1, howver if Bank_SL_EQ doesn't start on row 1 it would not work.

I think it should be something like

ROW(1:12) where Bank_SL_EQ is 12 rows high.

So, IMHO (happy to stand corrected), but the second instance should be (independent of whether table starts on row 1 or another row:

{=INDEX(INDEX(Bank_SL_EQ,,2),SMALL(IF(INDEX(Bank_SL_EQ,,1)=$H19,ROW(OFFSET($A$1,,,ROWS(Bank_SL_EQ))),""),2),1)}

See attached & thanks for the lesson, as always, I learn alot from you.

Dave
findNthInstanceVlookup.xls
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 37724491
Dave,

Yes, my "Nth instance" formulae require an adjustment if the range does not start in Row 1.  Fortunately, the adjustment is very simple.  This would be the 2nd instance formula:

{=INDEX(INDEX(Bank_SL_EQ,,5),SMALL(IF(INDEX(Bank_SL_EQ,,1)=Q2,ROW(INDEX(Bank_SL_EQ,,1))-1,""),2),1)}

The above assumes that the range starts in Row 2.  See how I applied a -1 adjustment to the ROW() call.  Had the range started in Row 3, then a -2 adjustment would be needed.

:)

Patrick
0
 

Author Comment

by:IO_Dork
ID: 38783407
matthewspatrick:

how would I modify {=MAX(IF(INDEX(Bank_SL_EQ,,1)=Q2,INDEX(Bank_SL_EQ,,5),""))} to lookup the max value of column 5 of the named range but to then return the contents of column 18?

Not sure if I need to reopen a new question.  let me know and I will.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now