find last row value in column when using MATCH to find column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bouce - 06 Feb 2008 16:43 GMT

Hi

I have a problem where I use MATCH to find a column with a specific heading
and then I want to find the last value in the column.
I would like to achieve this just using formulas in the worksheet if possible
All the solutions I have found require the Column letter ("B") instead of a
reference.
My match range does start at column A as 1 if that helps.
Any assistance would be greatly appreciated.
Thanks

Reply to this Message

Gaurav - 06 Feb 2008 16:57 GMT

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)

This will pull the last value in the column.

> Hi
>
[quoted text clipped - 9 lines]
> Any assistance would be greatly appreciated.
> Thanks

Reply to this Message

Bouce - 06 Feb 2008 17:04 GMT

Thanks, I had found that but I don't have the definite "A" reference, it
could be any column in the range.

> =LOOKUP(2,1/(A1:A65535<>""),A1:A65535)
>
[quoted text clipped - 13 lines]
> > Any assistance would be greatly appreciated.
> > Thanks

Reply to this Message

Gaurav - 06 Feb 2008 17:11 GMT

yeah..it was just an example.

> Thanks, I had found that but I don't have the definite "A" reference, it
> could be any column in the range.
[quoted text clipped - 17 lines]
>> > Any assistance would be greatly appreciated.
>> > Thanks

Reply to this Message

Pete_UK - 06 Feb 2008 17:39 GMT

Your MATCH formula will return the relative position of the cell in
the horizontal range you use. If this number is less than 27, then you
can convert the number to a letter by means of:

CHAR(x+64)

where x is the output from your MATCH formula. Perhaps you can then
incorporate this letter in your formula by means of the INDIRECT
function, along the lines of:

=LOOKUP(2,1/(INDIRECT(CHAR(x+64)&"1:"&CHAR(x
+64)&"65535")<>""),INDIRECT(CHAR(x+64)&"1:"&CHAR(x+64)&"65535"))

Hope this helps.

Pete

> Thanks, I had found that but I don't have the definite "A" reference, it
> could be any column in the range.
[quoted text clipped - 18 lines]
>
> - Show quoted text -

Reply to this Message

Tom Hutchins - 06 Feb 2008 17:43 GMT

If I understand correctly what you are trying to do, a formula like the
following should work:

=LOOKUP(1E+307,INDIRECT(ADDRESS(1,MATCH("CAT",2:2))):INDIRECT(ADDRESS(65535,MATCH("CAT",2:2))))

In this example, the MATCH functions are looking for the heading "CAT" in
row 2. The LOOKUP function is searching rows 1 through 65535 of that column
for a ridiculously big number (1E+307), and returns the last number found in
the range. If there are no numbers in the range, an error is returned.

Hope this helps,

Hutch

> Hi
>
[quoted text clipped - 6 lines]
> Any assistance would be greatly appreciated.
> Thanks

Reply to this Message

T. Valko - 06 Feb 2008 22:16 GMT

I'm assuming that "value" is numeric.

See this screencap:

http://img122.imageshack.us/img122/9540/lastnumft7.jpg

Signature

Biff
Microsoft Excel MVP

> Hi
>
[quoted text clipped - 9 lines]
> Any assistance would be greatly appreciated.
> Thanks

Reply to this Message