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

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

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

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