vLookup Problem never ceases
Dear someone
Im having a problem with a vlookup formula, with (last cell/row always
varying) what do I do to copy the last cell with data and paste it to another
sheet.
This formula below finds the first sum not the last.
Or Is there another way to copy last cell with data and paste to another
sheet?

=IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE))

If you review the function..

=VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup)

The col_index_num is the column number within the table_array. Here there
are only 28 columns in the table array $A$5:$AF$32. If you are looking to
retrieve the last column of the above array it should be 28 instead of 32.

=VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE)



If this post helps click Yes
---------------
Jacob Skaria


"aussiegirlone" wrote:

vLookup Problem never ceases
Dear someone
Im having a problem with a vlookup formula, with (last cell/row always
varying) what do I do to copy the last cell with data and paste it to another
sheet.
This formula below finds the first sum not the last.
Or Is there another way to copy last cell with data and paste to another
sheet?

=IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE))

Im sure I can count the number of columns and it is 32, however I copied your
formula and it returns 0 so I change 28 to 32 and it returned the first sum
instead of the last which is the total.
6
6
4
5
21

I have tried this code but the problem is it adds up all the sums including
the total so I get double the total e.g 42 should be 21

=SUM(Employee1!AF:AF)


"Jacob Skaria" wrote:

If you review the function..

=VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup)

The col_index_num is the column number within the table_array. Here there
are only 28 columns in the table array $A$5:$AF$32. If you are looking to
retrieve the last column of the above array it should be 28 instead of 32.

=VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE)



If this post helps click Yes
---------------
Jacob Skaria


"aussiegirlone" wrote:

vLookup Problem never ceases
Dear someone
Im having a problem with a vlookup formula, with (last cell/row always
varying) what do I do to copy the last cell with data and paste it to another
sheet.
This formula below finds the first sum not the last.
Or Is there another way to copy last cell with data and paste to another
sheet?

=IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE))

Hi

As the last cell in column AF is a total, I suppose you have a row heading
in the total line column A equal to "Total", try this sumif formula:

=SUMIF(Employee1!A5:A32;"<Total";Employee1!AF5:AF 32)

Hopes this helps.
....
Per

"aussiegirlone" skrev i
meddelelsen ...
Im sure I can count the number of columns and it is 32, however I copied
your
formula and it returns 0 so I change 28 to 32 and it returned the first
sum
instead of the last which is the total.
6
6
4
5
21

I have tried this code but the problem is it adds up all the sums
including
the total so I get double the total e.g 42 should be 21

=SUM(Employee1!AF:AF)


"Jacob Skaria" wrote:

If you review the function..

=VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup)

The col_index_num is the column number within the table_array. Here there
are only 28 columns in the table array $A$5:$AF$32. If you are looking to
retrieve the last column of the above array it should be 28 instead of
32.

=VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE)



If this post helps click Yes
---------------
Jacob Skaria


"aussiegirlone" wrote:

vLookup Problem never ceases
Dear someone
Im having a problem with a vlookup formula, with (last cell/row always
varying) what do I do to copy the last cell with data and paste it to
another
sheet.
This formula below finds the first sum not the last.
Or Is there another way to copy last cell with data and paste to
another
sheet?

=IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE))

Oops...Sorry ...i would have been in another world while replying earlier..

Below mentioned is one way to retrieve the last value from AF for the
matching valuue in Col A.

'array formula
=INDIRECT(ADDRESS(MAX(IF(Employee1!$A$5:$A$32=A5,R OW(Employee1!$A$5:$A$32))),32))

If this post helps click Yes
---------------
Jacob Skaria


"aussiegirlone" wrote:

Im sure I can count the number of columns and it is 32, however I copied your
formula and it returns 0 so I change 28 to 32 and it returned the first sum
instead of the last which is the total.
6
6
4
5
21

I have tried this code but the problem is it adds up all the sums including
the total so I get double the total e.g 42 should be 21

=SUM(Employee1!AF:AF)


"Jacob Skaria" wrote:

If you review the function..

=VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup)

The col_index_num is the column number within the table_array. Here there
are only 28 columns in the table array $A$5:$AF$32. If you are looking to
retrieve the last column of the above array it should be 28 instead of 32.

=VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE)



If this post helps click Yes
---------------
Jacob Skaria


"aussiegirlone" wrote:

vLookup Problem never ceases
Dear someone
Im having a problem with a vlookup formula, with (last cell/row always
varying) what do I do to copy the last cell with data and paste it to another
sheet.
This formula below finds the first sum not the last.
Or Is there another way to copy last cell with data and paste to another
sheet?

=IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE))

I dont understand why the codes you both have provided still returns 0

"Jacob Skaria" wrote:

Oops...Sorry ...i would have been in another world while replying earlier..

Below mentioned is one way to retrieve the last value from AF for the
matching valuue in Col A.

'array formula
=INDIRECT(ADDRESS(MAX(IF(Employee1!$A$5:$A$32=A5,R OW(Employee1!$A$5:$A$32))),32))

If this post helps click Yes
---------------
Jacob Skaria


"aussiegirlone" wrote:

Im sure I can count the number of columns and it is 32, however I copied your
formula and it returns 0 so I change 28 to 32 and it returned the first sum
instead of the last which is the total.
6
6
4
5
21

I have tried this code but the problem is it adds up all the sums including
the total so I get double the total e.g 42 should be 21

=SUM(Employee1!AF:AF)


"Jacob Skaria" wrote:

If you review the function..

=VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup)

The col_index_num is the column number within the table_array. Here there
are only 28 columns in the table array $A$5:$AF$32. If you are looking to
retrieve the last column of the above array it should be 28 instead of 32.

=VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE)



If this post helps click Yes
---------------
Jacob Skaria


"aussiegirlone" wrote:

vLookup Problem never ceases
Dear someone
Im having a problem with a vlookup formula, with (last cell/row always
varying) what do I do to copy the last cell with data and paste it to another
sheet.
This formula below finds the first sum not the last.
Or Is there another way to copy last cell with data and paste to another
sheet?

=IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE))

OK. Let us try the below.

With a table as below try the below formula which retrives the last matching
value for 'test'. You can adjust this to suit for your requirement..

ColA ColB
test 1
apple 32
test 2
apple 45
apple 52
boy 23
test 3

=LOOKUP(2,1/(A1:A10="test"),B1:B10)

If this post helps click Yes
---------------
Jacob Skaria


"aussiegirlone" wrote:

I dont understand why the codes you both have provided still returns 0

"Jacob Skaria" wrote:

Oops...Sorry ...i would have been in another world while replying earlier..

Below mentioned is one way to retrieve the last value from AF for the
matching valuue in Col A.

'array formula
=INDIRECT(ADDRESS(MAX(IF(Employee1!$A$5:$A$32=A5,R OW(Employee1!$A$5:$A$32))),32))

If this post helps click Yes
---------------
Jacob Skaria


"aussiegirlone" wrote:

Im sure I can count the number of columns and it is 32, however I copied your
formula and it returns 0 so I change 28 to 32 and it returned the first sum
instead of the last which is the total.
6
6
4
5
21

I have tried this code but the problem is it adds up all the sums including
the total so I get double the total e.g 42 should be 21

=SUM(Employee1!AF:AF)


"Jacob Skaria" wrote:

If you review the function..

=VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup)

The col_index_num is the column number within the table_array. Here there
are only 28 columns in the table array $A$5:$AF$32. If you are looking to
retrieve the last column of the above array it should be 28 instead of 32.

=VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE)



If this post helps click Yes
---------------
Jacob Skaria


"aussiegirlone" wrote:

vLookup Problem never ceases
Dear someone
Im having a problem with a vlookup formula, with (last cell/row always
varying) what do I do to copy the last cell with data and paste it to another
sheet.
This formula below finds the first sum not the last.
Or Is there another way to copy last cell with data and paste to another
sheet?

=IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE))

Aussiegirlone, can you supply a sample workbook?

aussiegirlone;421660 Wrote:
I dont understand why the codes you both have provided still returns 0

"Jacob Skaria" wrote:

Oops...Sorry ...i would have been in another world while replying
earlier..

Below mentioned is one way to retrieve the last value from AF for the
matching valuue in Col A.

'array formula

=INDIRECT(ADDRESS(MAX(IF(Employee1!$A$5:$A$32=A5,R OW(Employee1!$A$5:$A$32))),32))

If this post helps click Yes
---------------
Jacob Skaria


"aussiegirlone" wrote:

Im sure I can count the number of columns and it is 32, however I
copied your
formula and it returns 0 so I change 28 to 32 and it returned the
first sum
instead of the last which is the total.
6
6
4
5
21

I have tried this code but the problem is it adds up all the sums
including
the total so I get double the total e.g 42 should be 21

=SUM(Employee1!AF:AF)


"Jacob Skaria" wrote:

If you review the function..

=VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup)

The col_index_num is the column number within the table_array.
Here there
are only 28 columns in the table array $A$5:$AF$32. If you are
looking to
retrieve the last column of the above array it should be 28
instead of 32.

=VLOOKUP(A5,Employee1!$A$5:$AF$32,28,FALSE)



If this post helps click Yes
---------------
Jacob Skaria


"aussiegirlone" wrote:

vLookup Problem never ceases
Dear someone
Im having a problem with a vlookup formula, with (last
cell/row always
varying) what do I do to copy the last cell with data and paste
it to another
sheet.
This formula below finds the first sum not the last.
Or Is there another way to copy last cell with data and paste
to another
sheet?


=IF(ISNA(VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE )),"",VLOOKUP(A5,Employee1!$A$5:$AF$32,32,FALSE))



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117308