Thanks Rick ... tend to forget those absolutes.
And re-reading the OP, you're probably right there also.<bg
Let's see if you get any feed-back.
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Rick Rothstein (MVP -
VB)" wrote in
message ...
It is a little hard to tell for sure, but I think you are finding the
wrong
match... it looked to me that he wanted the month for the **first** "Sold"
cell in the last group of contiguous "Sold" cells.
In any event, I just wanted to point out the B1:M1 reference inside the
INDEX function should be changed to B$1:M$1 to allow the formula to be
able
to be copied down.
Rick
"Ragdyer" wrote in message
...
Try this *array* formula in N2, if there is the possibility that the
rows
might contain *other* data, in addition to the text "sold":
=IF(ISNA(MATCH("sold",B2:M2)),"",INDEX(B1:M1,MATCH (2,1/(B2:M2="sold"))))
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,
instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.
*After the CSE entry, copy down as needed.
--
HTH,
RD
--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!
--------------------------------------------------------------------------
-
"Mac" wrote in message
...
I have a problem I am trying to solve, which is hard to explain:
I have a spreadsheet that in row 1 starting in column B has the
months:
Jan Feb Mar Apr ...........Dec (Columns B to M), in column N It has
"Last
start selling"
The cells below the month show when the items have been sold.... in
Column
N. I would like a formula in Column N (Last Start Selling) which
keeps
on
searching for the word Sold until the next column to the left is Blank.
The it stops and picks up the month from row 1 (this is the easy
part...)
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Last
start selling
Item1 Sold
Sold
Sold Oct
Item 2 Sold
Sold
Sold Dec
Item 3 Sold SoldSold Sold
Jun
I hope this makes sense....
Thanks,
mac