MatchLast function
 

I have a column C containing the following:

=IF($B37" ",MatchLast($B37,$B$2:$B36,2)," ")


I meant this to find the last entry in column B that matches $B37 -
and move its corresponding C value in.

For instance, it would move "4" into C37. But I'm getting "5" moved
in. I entered MatchLast in my Excel 2003 help and it did not find a
match.

34 x test1 5
35 x test1 4
36 x test2 6
37 x test1 ????

How should I change this formula?

MatchLast function
 

Hello, try this in cell C37

=INDIRECT("$C$"&ROW(INDEX($B$2:$B$36,MAX(($B$2:$B$ 36=$C$37)*ROW($B$2:$B
$36))-ROW(OFFSET($B$2:$B$36,0,0,1,1))+1)))

MatchLast function
 

Give this a try...

=IF(COUNTIF($B1:$B37,$B37)1,INDEX(C1:C36,SUMPRODU CT(MAX(ROW(1:36)*($B1:$B36=$B37)))),"")

Rick


"Howard Brazee" wrote in message
...
I have a column C containing the following:

=IF($B37" ",MatchLast($B37,$B$2:$B36,2)," ")


I meant this to find the last entry in column B that matches $B37 -
and move its corresponding C value in.

For instance, it would move "4" into C37. But I'm getting "5" moved
in. I entered MatchLast in my Excel 2003 help and it did not find a
match.

34 x test1 5
35 x test1 4
36 x test2 6
37 x test1 ????

How should I change this formula?

MatchLast function
 

On Aug 1, 4:17*pm, GTVT06 wrote:
Hello, try this in cell C37

=INDIRECT("$C$"&ROW(INDEX($B$2:$B$36,MAX(($B$2:$B$ 36=$C$37)*ROW($B$2:$B
$36))-ROW(OFFSET($B$2:$B$36,0,0,1,1))+1)))

Forgot to tell you this has to be entered in as an array using Ctrl
+Shift+Enter after you put in the formula. this should encase the
formula in { } brackets when entered in correctly.

MatchLast function
 

On Fri, 01 Aug 2008 14:55:08 -0600, Howard Brazee
wrote:

I have a column C containing the following:

=IF($B37" ",MatchLast($B37,$B$2:$B36,2)," ")


I meant this to find the last entry in column B that matches $B37 -
and move its corresponding C value in.

For instance, it would move "4" into C37. But I'm getting "5" moved
in. I entered MatchLast in my Excel 2003 help and it did not find a
match.

34 x test1 5
35 x test1 4
36 x test2 6
37 x test1 ????

How should I change this formula?


If you always have at least one match, you may try this formula in
cell C37:

=IF($B37"",INDEX(C$1:C36,LARGE(ROW(B$2:B$36)*($B3 7=$B$2:$B36),1)),"")

Hope this helps / Lars-Åke

MatchLast function
 

One mo

=if($b37="","",LOOKUP(2,1/($B$2:$B$36=$B37),$C$2:$C$36))

I like empty strings--not single space characters--to make the cell look empty.




Howard Brazee wrote:

I have a column C containing the following:

=IF($B37" ",MatchLast($B37,$B$2:$B36,2)," ")

I meant this to find the last entry in column B that matches $B37 -
and move its corresponding C value in.

For instance, it would move "4" into C37. But I'm getting "5" moved
in. I entered MatchLast in my Excel 2003 help and it did not find a
match.

34 x test1 5
35 x test1 4
36 x test2 6
37 x test1 ????

How should I change this formula?

--

Dave Peterson

MatchLast function
 

I just thought I would make a note that I used my

IF(COUNTIF($B1:$B37,$B37)1...

test instead of a test similar to this...

IF($B37=""...

in order to stop the #N/A error that gets generated if the text in B37 was
not empty, but also didn't appear in any of the earlier cells of Column B.

Rick


"Dave Peterson" wrote in message
...
One mo

=if($b37="","",LOOKUP(2,1/($B$2:$B$36=$B37),$C$2:$C$36))

I like empty strings--not single space characters--to make the cell look
empty.




Howard Brazee wrote:

I have a column C containing the following:

=IF($B37" ",MatchLast($B37,$B$2:$B36,2)," ")

I meant this to find the last entry in column B that matches $B37 -
and move its corresponding C value in.

For instance, it would move "4" into C37. But I'm getting "5" moved
in. I entered MatchLast in my Excel 2003 help and it did not find a
match.

34 x test1 5
35 x test1 4
36 x test2 6
37 x test1 ????

How should I change this formula?

--

Dave Peterson

MatchLast function
 

On Fri, 1 Aug 2008 14:20:07 -0700 (PDT), GTVT06
wrote:

On Aug 1, 4:17*pm, GTVT06 wrote:
Hello, try this in cell C37

=INDIRECT("$C$"&ROW(INDEX($B$2:$B$36,MAX(($B$2:$B$ 36=$C$37)*ROW($B$2:$B
$36))-ROW(OFFSET($B$2:$B$36,0,0,1,1))+1)))

I got a circular reference.

Forgot to tell you this has to be entered in as an array using Ctrl
+Shift+Enter after you put in the formula. this should encase the
formula in { } brackets when entered in correctly.

I'm not familiar with this, but I copied the formula to a text editor,
moved it to one line, then copied it to cell C37, pasted it, hit
Ctl/Shift/Enter and it put brackets around it. But it displayed a
zero.

Could you explain what this did, so that I can try to debug it?
Thanks.

MatchLast function
 

On Fri, 1 Aug 2008 17:17:39 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

Give this a try...

=IF(COUNTIF($B1:$B37,$B37)1,INDEX(C1:C36,SUMPROD UCT(MAX(ROW(1:36)*($B1:$B36=$B37)))),"")

Rick

That worked for B37, but I neglected to say that this is a spreadsheet
that is growing. Every week I add a new line. I'm up to line 50
now. Copying B37 down got bad references.

When I get a new value, I overwrite this formula with the new value.

MatchLast function
 

On Fri, 01 Aug 2008 21:23:42 GMT, Lars-Åke Aspelin
wrote:

If you always have at least one match, you may try this formula in
cell C37:

=IF($B37"",INDEX(C$1:C36,LARGE(ROW(B$2:B$36)*($B 37=$B$2:$B36),1)),"")

I put in a new row each week, whenever there is a new value in the B
column, I overtype the C formula with its corresponding new value.

My first set of values is in row 3, I have the formula currently set
to go to row 100, although I only have the first 50 rows populated
with data. Your formula displayed #### for me.

MatchLast function
 

On Fri, 01 Aug 2008 19:52:13 -0500, Dave Peterson
wrote:

One mo

=if($b37="","",LOOKUP(2,1/($B$2:$B$36=$B37),$C$2:$C$36))

I like empty strings--not single space characters--to make the cell look empty.

That appears to work for me, thanks.

MatchLast function
 

On Mon, 04 Aug 2008 07:43:10 -0600, Howard Brazee
wrote:

On Fri, 1 Aug 2008 14:20:07 -0700 (PDT), GTVT06
wrote:

On Aug 1, 4:17*pm, GTVT06 wrote:
Hello, try this in cell C37

=INDIRECT("$C$"&ROW(INDEX($B$2:$B$36,MAX(($B$2:$B$ 36=$C$37)*ROW($B$2:$B
$36))-ROW(OFFSET($B$2:$B$36,0,0,1,1))+1)))

I got a circular reference.

Forgot to tell you this has to be entered in as an array using Ctrl
+Shift+Enter after you put in the formula. this should encase the
formula in { } brackets when entered in correctly.

I'm not familiar with this, but I copied the formula to a text editor,
moved it to one line, then copied it to cell C37, pasted it, hit
Ctl/Shift/Enter and it put brackets around it. But it displayed a
zero.

Could you explain what this did, so that I can try to debug it?
Thanks.


Try changing the $C$37 to $B$36 in the formula above.
(The formula should still be in cell C37)

Lars-Åke

MatchLast function
 

On Mon, 04 Aug 2008 14:22:03 GMT, Lars-Åke Aspelin
wrote:

On Mon, 04 Aug 2008 07:43:10 -0600, Howard Brazee
wrote:

On Fri, 1 Aug 2008 14:20:07 -0700 (PDT), GTVT06
wrote:

On Aug 1, 4:17*pm, GTVT06 wrote:
Hello, try this in cell C37

=INDIRECT("$C$"&ROW(INDEX($B$2:$B$36,MAX(($B$2:$B$ 36=$C$37)*ROW($B$2:$B
$36))-ROW(OFFSET($B$2:$B$36,0,0,1,1))+1)))

I got a circular reference.

Forgot to tell you this has to be entered in as an array using Ctrl
+Shift+Enter after you put in the formula. this should encase the
formula in { } brackets when entered in correctly.

I'm not familiar with this, but I copied the formula to a text editor,
moved it to one line, then copied it to cell C37, pasted it, hit
Ctl/Shift/Enter and it put brackets around it. But it displayed a
zero.

Could you explain what this did, so that I can try to debug it?
Thanks.


Try changing the $C$37 to $B$36 in the formula above.
(The formula should still be in cell C37)

Lars-Åke

Sorry, I meant, "change $C$37 to $B$37"

MatchLast function
 

On Mon, 04 Aug 2008 07:54:19 -0600, Howard Brazee
wrote:

One mo

=if($b37="","",LOOKUP(2,1/($B$2:$B$36=$B37),$C$2:$C$36))

I like empty strings--not single space characters--to make the cell look empty.

That appears to work for me, thanks.

Nope, it worked plugging some in. Then I copied it to cells C3
through C100 and filled in the values by hand for C3, C4, C8, C27,
C35, & C43 (which correspond to new B3, B4, B8, B27, B35, & B43
values), and it didn't work. So I changed cell C37 to be:
=if($b37="","",LOOKUP(2,1/($B$3:$B36=$B37),$C$3:$C36))

and then copied it up and down from C5-C100 (keeping my overwritten C3
& C4).

The other new values showed ##N/A until I populated them (overwriting
the formula with the values). The correct figures are populating,
thanks.

MatchLast function
 

On Mon, 04 Aug 2008 07:51:36 -0600, Howard Brazee
wrote:

On Fri, 01 Aug 2008 21:23:42 GMT, Lars-Åke Aspelin
wrote:

If you always have at least one match, you may try this formula in
cell C37:

=IF($B37"",INDEX(C$1:C36,LARGE(ROW(B$2:B$36)*($ B37=$B$2:$B36),1)),"")

I put in a new row each week, whenever there is a new value in the B
column, I overtype the C formula with its corresponding new value.

My first set of values is in row 3, I have the formula currently set
to go to row 100, although I only have the first 50 rows populated
with data. Your formula displayed #### for me.

I forgot to mention that this formula has to be entered as an array
formula, i.e. with CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke