Find max in array, return corresponding value in array, without max function

**cshwkhelp** · 03-06-2017, 08:06 PM

Basically what the title says. I have to make a function that takes in two arrays, and then it checks the max numerical value in the first array and spits out the corresponding text from array 2. How can I do this without using the max function?

**jolivanes** · 03-06-2017, 10:15 PM

How many items are going to be in these arrays?
And what is the reason that "max" cannot be used?

This might be a start.
Change references where required

Sub Who_Knows()
Dim a, b, i As Long, j As Double, jj As Long

a = Range("J1:J" & Cells(Rows.Count, 10).End(xlUp).Row).Value
b = Range("K1:K" & Cells(Rows.Count, 11).End(xlUp).Row).Value
j = a(1, 1)
jj = 1

    For i = 2 To UBound(a, 1)
        If a(i, 1) > j Then j = a(i, 1): jj = i
    Next i
    
MsgBox b(jj, 1)
End Sub

**MrShorty** · 03-06-2017, 11:20 PM

The LARGE() function could be used in place of the MAX() function, but, I don't see the reason for avoiding the use of the MAX() function. https://support.office.com/en-us/art...b-01672ec00a64

**cshwkhelp** · 03-07-2017, 12:39 AM

Originally Posted by MrShorty

I don't see the reason for avoiding the use of the MAX() function. https://support.office.com/en-us/art...b-01672ec00a64

It's for a school assignment, but I don't really like the teacher so instead of going to her for help, I'm coming here. I've been stuck here for a while lol.

**jolivanes** · 03-07-2017, 11:26 PM

All you have to do now is hoping she does not read this and compares your solution to the suggestions here.

**mike7952** · 03-06-2017, 11:58 PM

Like this

 Dim arr1, arr2
 
 arr1 = Array(3, 5, 1, 4, 0, 2)
 arr2 = Array("Apple", "Orange", "Bananna", "Peach", "Plum", "Pie")
 
 MsgBox arr2(WorksheetFunction.Large(arr1, 1))

Find max in array, return corresponding value in array, without max function

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Find max in array, return corresponding value in array, without max function

Re: Find max in array, return corresponding value in array, without max function

Re: Find max in array, return corresponding value in array, without max function

Re: Find max in array, return corresponding value in array, without max function

Re: Find max in array, return corresponding value in array, without max function

Re: Find max in array, return corresponding value in array, without max function

Thread Information

Users Browsing this Thread

Similar Threads

UDF array function doesn't return array

How to find within an array, and return AVG of certain cells

Find Cells in Array and Return Value Next to It

[SOLVED] Find Value in array, and return what is on the cell.

Find Date in Array. Return Value to it

Find a value in an array and return multiple values in an adjacent array

[SOLVED] Use FIND to return an array of items

Bookmarks

Bookmarks

Posting Permissions