Hi,

I have a list of part numbers that show cost prices by month for 2008. I
need to pull the last cost for 2008. Problem is, if there was no cost change
for a particular month, then there is no entry in that cell for that month.
The last cost for some parts may be in October or June, etc. Is there a
formula that can look down the row and pull the last cost no matter what
month it lands in?

Thanks,

Richard,

Maybe this. It finds the latest date in column A and returns Column B

=INDEX(B:B,MATCH(MAX(A:A),A:A,0))

Mike

"Richard" wrote:

Hi,

I have a list of part numbers that show cost prices by month for 2008. I
need to pull the last cost for 2008. Problem is, if there was no cost change
for a particular month, then there is no entry in that cell for that month.
The last cost for some parts may be in October or June, etc. Is there a
formula that can look down the row and pull the last cost no matter what
month it lands in?

Thanks,

Mike,

The information is in rows with the row header being the months, Jan-Dec.
So How do I start in Dec and look back to Jan to find the latest cost?

Thanks,

"Mike H" wrote:

Richard,

Maybe this. It finds the latest date in column A and returns Column B

=INDEX(B:B,MATCH(MAX(A:A),A:A,0))

Mike

"Richard" wrote:

Hi,

I have a list of part numbers that show cost prices by month for 2008. I
need to pull the last cost for 2008. Problem is, if there was no cost change
for a particular month, then there is no entry in that cell for that month.
The last cost for some parts may be in October or June, etc. Is there a
formula that can look down the row and pull the last cost no matter what
month it lands in?

Thanks,

I'm assuming that "Part No" is in column A and Jan-Dec = columns B:M where
the header row = 1. This would place your first part number and costs in row
2.
Place this formula in N2 (or any column thats not in table)
=INDEX(B2:M2,1,MAX(IF(LEN(B2:M2)0,COLUMN(B2:M2)-(COLUMN(B2)-1),0)))
This is an array formula, so you will need to hit Control-Shift-Enter
instead of Enter to make it an array formula. (If you do this correctly, {}
brackets will appear around the formula.)
After the formula is entered you can copy the formula down as needed. (Don't
try to paste over the original formula.)
This will give you the value in the last used cell in the table row.
Hope this helps.

"Richard" wrote in message
...
Mike,

The information is in rows with the row header being the months, Jan-Dec.
So How do I start in Dec and look back to Jan to find the latest cost?

Thanks,

"Mike H" wrote:

Richard,

Maybe this. It finds the latest date in column A and returns Column B

=INDEX(B:B,MATCH(MAX(A:A),A:A,0))

Mike

"Richard" wrote:

Hi,

I have a list of part numbers that show cost prices by month for 2008.
I
need to pull the last cost for 2008. Problem is, if there was no cost
change
for a particular month, then there is no entry in that cell for that
month.
The last cost for some parts may be in October or June, etc. Is there
a
formula that can look down the row and pull the last cost no matter
what
month it lands in?

Thanks,

This will return the rightmost (last) numeric value in the range B2:M2.

=LOOKUP(1E100,B2:M2)

This will return the corresponding column header from the range B1:M1.

=LOOKUP(1E100,B2:M2,B1:M1)

--
Biff
Microsoft Excel MVP


"Richard" wrote in message
...
Hi,

I have a list of part numbers that show cost prices by month for 2008. I
need to pull the last cost for 2008. Problem is, if there was no cost
change
for a particular month, then there is no entry in that cell for that
month.
The last cost for some parts may be in October or June, etc. Is there a
formula that can look down the row and pull the last cost no matter what
month it lands in?

Thanks,