I have a cell that looks like this:
=IF(B23=B22,C22,"")

But what I really want is a Match function that finds the last match
between B23 and any row above it, and then copies the value in column
C to C23.

e.g.
B C
19 red 3
20 green 5
21 red 6
22 blue 17


How do I get C23 to enter a 6 when I enter "red" in B23?

Note: This spreadsheet will grow, I add a new row or two every week.

Here is a user-defined function. You may want to omit the 3rd argument
and use
MatchLast = LookupArray(iRow, 2).

Function MatchLast(LookupValue, LookupArray As Range, Column As
Integer)
Dim c As Range
Dim iRow As Integer
For iRow = LookupArray.Rows.Count To 1 Step -1
If LookupArray(iRow, 1) = LookupValue Then
MatchLast = LookupArray(iRow, Column)
Exit Function
End If
Next iRow
End Function

Hth,
Merjet

On 10 May 2007 08:42:05 -0700, merjet wrote:

Here is a user-defined function. You may want to omit the 3rd argument
and use
MatchLast = LookupArray(iRow, 2).


I actually will be populating quite a few cells with values on more
than one tabbed page. I'm not sure whether that's an argument for a
3rd argument.

I saved the macro (it wanted to make it a sub), and haven't quite got
the cell working.

On Thu, 10 May 2007 10:00:28 -0600, Howard Brazee
wrote:

I saved the macro (it wanted to make it a sub), and haven't quite got
the cell working.

I get a compile error, expected type name. I suspect I'm not passing
something correctly.

I saved the macro (it wanted to make it a sub), and haven't
quite got the cell working.

Post your code. Some cell data would help, too.

Merjet

On 10 May 2007 09:12:56 -0700, merjet wrote:

I saved the macro (it wanted to make it a sub), and haven't
quite got the cell working.

Post your code. Some cell data would help, too.

Merjet



I'm not familiar with user created functions, so I didn't want to get
fancy.

My cell's value has:
=MatchLast($B24, $B$2:$B23, 3)

for now, just to see what I would get. I guessed that "column as
integer" meant, translate C to 3.

When I copied in your code, I selected Tools/Macro/Macros, entered in
MatchLast, erased the stub code for a subroutine, and pasted in your
function. That may have been the wrong thing to do, but I looked
all over for how to enter a function. (I found a place for recording
a macro - maybe I should have done that and then edited it down).

I told it that it is a global macro.