Search Columns for a word and stop
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
|