Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last row value in column when using MATCH to find column
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last row value in column when using MATCH to find column
=LOOKUP(2,1/(A1:A65535<""),A1:A65535)
This will pull the last value in the column. "Bouce" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last row value in column when using MATCH to find column
Thanks, I had found that but I don't have the definite "A" reference, it
could be any column in the range. "Gaurav" wrote: =LOOKUP(2,1/(A1:A65535<""),A1:A65535) This will pull the last value in the column. "Bouce" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last row value in column when using MATCH to find column
yeah..it was just an example.
"Bouce" wrote in message ... Thanks, I had found that but I don't have the definite "A" reference, it could be any column in the range. "Gaurav" wrote: =LOOKUP(2,1/(A1:A65535<""),A1:A65535) This will pull the last value in the column. "Bouce" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last row value in column when using MATCH to find column
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 On Feb 6, 5:04*pm, Bouce wrote: Thanks, I had found that but I don't have the definite "A" reference, it could be any column in the range. "Gaurav" wrote: =LOOKUP(2,1/(A1:A65535<""),A1:A65535) This will pull the last value in the column. "Bouce" wrote in message ... 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- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last row value in column when using MATCH to find column
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 "Bouce" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find last row value in column when using MATCH to find column
I'm assuming that "value" is numeric.
See this screencap: http://img122.imageshack.us/img122/9540/lastnumft7.jpg -- Biff Microsoft Excel MVP "Bouce" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find something in column a then find if column B matches criteria | Excel Discussion (Misc queries) | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
How can find match of one cell from a column of cells? | Excel Discussion (Misc queries) | |||
find LAST match in column | Excel Worksheet Functions | |||
find LAST match in column | Excel Worksheet Functions |