Find the last cell in a column which contains "-"

  • Thread starter Thread starter dhstein
  • Start date Start date
D

dhstein

I have a column of values that contain text like XX-YY, ZZZ-DD4, etc. I want
to find the last row of the column that has a "-". Is there a function I can
use to find this (maybe SUMPRODUCT ?). Thanks for any help with this.
 
To return the last value containing "-", try...

=LOOKUP(9.99999999999999E+307,FIND("-",A2:A100),A2:A100)
 
Thanks teethless and Domenic. That's great - but I don't want the value - I
want the row number. How could I get that ?
 
=LOOKUP(2,1/(ISNUMBER(FIND("-",A:A))),A:A)

No need for the ISNUMBER function. You can't reference the entire column in
the FIND function if you're using a version of Excel prior to Excel 2007.
 
Thanks for this information. I thought I knew every Excel function - but
Lookup was new to me. I tried to read the examples, but I'm not clear how
this LOOKUP function is working - I even did "evaluate function" - but still
not sure. What is the "1/" . I assume somehow we're getting errors of
divide by zero when FIND is not found. I would appreciate it if one of you
could explain it a little. Thanks.

David
 
What is the "1/" . I assume somehow we're getting
errors of divide by zero when FIND is not found.
Click to expand...

Yes, that is correct depending on which version of the formula you use. If
you use the 1/ISNUMBER version then you'll get #DIV/0! errors. If you use
the 1/FIND version then you'll get #VALUE! errors.

Here's how it works...

...........A.....
1....xx-xx
2...10
3...aa-bb
4...yes-no
5...dddd

=LOOKUP(2,1/FIND("-",A1:A5),A1:A5)

Result = yes-no

FIND returns the starting position of the character we're looking for in the
string. If the character is not found then the result is the error #VALUE!.

FIND("-",A1:A5)

FIND("-",A1) = 3 (the dash is found at the 3rd character)
FIND("-",A2) = #VALUE! (the dash is not found)
FIND("-",A3) = 3 (the dash is found at the 3rd character)
FIND("-",A4) = 4 (the dash is found at the 4th character)
FIND("-",A5) = #VALUE! (the dash is not found)

Each of these results is then divided:

1/FIND("-",A1:A5)

1/3 = 0.333
1/#VALUE! = #VALUE!
1/3 = 0.333
1/4 = 0.250
1/#VALUE! = #VALUE!

The way that LOOKUP works is if the lookup_value 2 is greater than all the
values in the lookup_vector it will match the *last* value in the
lookup_vector that is less than the lookup_value 2.

The *last* value in the lookup_vector that is less than the lookup_value 2
is
0.250. The #VALUE!! errors are ignored. We use a lookup_value of 2
because we know that the result of this expression:

1/FIND("-",A1:A5)

will not return a value greater than 1 thus ensuring that the lookup_value 2
is guaranteed to be greater than any value in the lookup_vector.

The final result of the formula is the value in the result_vector A1:A5 that
corresponds to the *last* value in the lookup_vector that is less than 2.

Lookup_vector...Result_vector
.......0.333................xx-xx
....#VALUE!............10
.......0.333................aa-bb
.......0.250................yes-no
....#VALUE!............dddd

So:

=LOOKUP(2,1/FIND("-",A1:A5),A1:A5)

Result = yes-no



exp101
 
Back
Top