Lookup

[Guest]

Formula
Jan Feb Mar Result
Part A 1 - - Jan
Part B - 1 - Feb
Part C - - 1 Mar

I need to write a formula that will return the last month a part was sold
over a three year period. In the table above the formula would return "Feb"
for part B. I can't do a nested if because I have to search through 36
columns.

Any thoughts are greatly appreciated.

 

[Guest]

One way which might suffice ..

Assuming source table as posted is in Sheet1,
with the "month" labels (Jan, Feb,...) in B1 across,
part labels in A2 down

In another sheet,
with the same part labels listed in A2 down

Place in B2, then array-enter the formula
by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=INDEX(Sheet1!$1:$1,MATCH(TRUE,ISNUMBER(Sheet1!2:2),0))
B2 will return the result "Jan". Copy down as far as required.

 

[Guest]

Sorry Max, I believe I over simplified the example. There will be multiple
results for each part accross 36 columns. I need to return the month from
the last instance. i.e. If the last part sold occured in Nov 2006, I need
the formula to return "Nov 2006", but there may be sales before this date.

Thanks again,
Eric

 

[Guest]

I'm out. Suggest you put in a new posting if no one else responds here.