MatchLast function

[Howard Brazee]

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?

 

[GTVT06]

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

 

[Rick Rothstein \(MVP - VB\)]

Give this a try...

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

Rick

 

[GTVT06]

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.

 

[Lars-Åke Aspelin]

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)*($B37=$B$2:$B36),1)),"")

Hope this helps / Lars-Åke

 

[Dave Peterson]

One more:

=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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Howard Brazee]

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.

 

[Howard Brazee]

Give this a try...

=IF(COUNTIF($B1:$B37,$B37)>1,INDEX(C1:C36,SUMPRODUCT(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.

 

[Howard Brazee]

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.

 

[Howard Brazee]

One more:

=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.

 

[Lars-Åke Aspelin]

I got a circular reference.


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

 

[Lars-Åke Aspelin]

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"

 

[Howard Brazee]

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.

 

[Lars-Åke Aspelin]

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