This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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)))

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,B

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
```

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

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

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,,

and used Ctrl+Shift+Enter

2) I copied R2

3) I select R3:R6 and hit Enter to do the paste

Q-27632466.xls

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

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

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

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.

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

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,,

3rd value:

{=INDEX(INDEX(Bank_SL_EQ,,

4th:

{=INDEX(INDEX(Bank_SL_EQ,,

Last value:

{=INDEX(INDEX(Bank_SL_EQ,,

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

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,,

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

Dave

findNthInstanceVlookup.xls

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,,

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

how would I modify {=MAX(IF(INDEX(Bank_SL_EQ,

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

{=MAX(IF(INDEX(Bank_SL_EQ,

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.