Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to lookup the last number and text in a column ?
Hi.
Lets consider the following table: A B 1 <------ Last item of the column either text or number 2 Amount Amount 3 250 125 4 500 365 6 5 120 not found 1500 350 458 5000 500 too costly Total 3250 2560 Result Fair The column A and B has both text , numbers and blank cells, May I know the formula to pick up the last item for ach column. Let the output be in cell A1 and B1 respectively. I try using =lookup (€śzzzz€ť, A2:A500), but it does not work. Kindly provide guidance Thanks Low -- A36B58K641 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to lookup the last number and text in a column ?
Hi,
For the last text or numeric value in column A use =INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A5000))*(A1:A 5000<"")))) Drag right and manually change the first A to a B for column B Mike "Mr. Low" wrote: Hi. Lets consider the following table: A B 1 <------ Last item of the column either text or number 2 Amount Amount 3 250 125 4 500 365 6 5 120 not found 1500 350 458 5000 500 too costly Total 3250 2560 Result Fair The column A and B has both text , numbers and blank cells, May I know the formula to pick up the last item for ach column. Let the output be in cell A1 and B1 respectively. I try using =lookup (€śzzzz€ť, A2:A500), but it does not work. Kindly provide guidance Thanks Low -- A36B58K641 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to lookup the last number and text in a column ?
formula to pick up the last item for ach column
Another play .. Try in A1: =LOOKUP(2,1/(A2:A500<""),A2:A500) Copy across to B1 or C1 as required -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:363 Subscribers:64 xdemechanik --- "Mr. Low" wrote: Lets consider the following table: A B 1 <------ Last item of the column either text or number 2 Amount Amount 3 250 125 4 500 365 6 5 120 not found 1500 350 458 5000 500 too costly Total 3250 2560 Result Fair The column A and B has both text , numbers and blank cells, May I know the formula to pick up the last item for ach column. Let the output be in cell A1 and B1 respectively. I try using =lookup (€śzzzz€ť, A2:A500), but it does not work |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to lookup the last number and text in a column ?
Hi,
Try this =LOOKUP(1,1/(B:B<""),B:B) If this helps, please click the Yes button. Cheers, Shane Devenshire "Mr. Low" wrote: Hi. Lets consider the following table: A B 1 <------ Last item of the column either text or number 2 Amount Amount 3 250 125 4 500 365 6 5 120 not found 1500 350 458 5000 500 too costly Total 3250 2560 Result Fair The column A and B has both text , numbers and blank cells, May I know the formula to pick up the last item for ach column. Let the output be in cell A1 and B1 respectively. I try using =lookup (€śzzzz€ť, A2:A500), but it does not work. Kindly provide guidance Thanks Low -- A36B58K641 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002: How to find 0.00 in a column of number? | Excel Discussion (Misc queries) | |||
2 criteria lookup of text. Return text form column 3. SUMPRODUCT t | Excel Worksheet Functions | |||
Excel 2002: How to convert number to text ? | Excel Discussion (Misc queries) | |||
How do I change the column ID in Excel from number to text? | Excel Discussion (Misc queries) | |||
Find Column Number via Lookup | Excel Worksheet Functions |