Excel matching question

[Howard Brazee]

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.

 

[merjet]

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

 

[Howard Brazee]

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.

 

[Howard Brazee]

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.

 

[merjet]

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

 

[Howard Brazee]

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.

 

[merjet]

It sounds like you put the code in the right place (in a standard
module in the VB Editor).

On a worksheet a user-defined function is used like an Excel function.

=MatchLast($B24, $B$2:$B23, 3) is fine syntax-wise. However, the
LookupArray you used is only one column -- it should be 2 or more --
and 3 means the 3rd column (of the LookupArray), which would only be
valid if the LookupArray had 3 or more columns.

Hth,
Merjet

 

[Howard Brazee]

It sounds like you put the code in the right place (in a standard
module in the VB Editor).

That's good.

On a worksheet a user-defined function is used like an Excel function.

=MatchLast($B24, $B$2:$B23, 3) is fine syntax-wise. However, the
LookupArray you used is only one column -- it should be 2 or more --
and 3 means the 3rd column (of the LookupArray), which would only be
valid if the LookupArray had 3 or more columns.

I don't understand what that means, and will have to do some research.
I opened my spreadsheet, and spent some time trying to remember how to
tell it that this macro is safe. I've done that in the past, and
remember it taking some work, but don't remember what I did. That
can wait though.

Since the number 3 is wrong, I tried replacing it with 2 and with 1,
without getting around the
Compile error:
Expected: type name

which pops up over my macro editor.

The first logical line below is red:
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

 

[merjet]

Compile error:

Expected: type name

which pops up over my macro editor.

The first logical line below is red:
Function MatchLast(LookupValue, LookupArray As Range, Column As
Integer)

In your VB Editor "Integer)" s/b on the same line as the one before
it.
When posted here, long lines can overflow to the next line.

Merjet

 

[Howard Brazee]

In your VB Editor "Integer)" s/b on the same line as the one before
it.
When posted here, long lines can overflow to the next line.

That corrected the problem. It didn't occur to me that VB as line
sensitive.

=MatchLast($B24, $B$2:$B23, 2)

appears to work for me. Now I need to find the macro and tell Excel
to trust it.

 

[merjet]

=MatchLast($B24, $B$2:$B23, 2)

That won't work. But $B$2:$C23 should.

Merjet