If I understand what you're saying, you're looking to return a value from
*various* columns, dependent on the row matching a criteria.
BUT ... what determines *which* row, since you have multiple rows matching
the criteria?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Clueless" wrote in message
...
Guys,

Thanks for the prompt response, but that is just the thing, I did not want
to use drop down formulas because it would take a lot of space. Perhaps
further explanation is necessary. The last column (E) is a numerical
value,
but is not the last column in my spreadsheet. As a matter of fact there
are
about 20 more columns with numerical data, each of course with different
information. In case there is a -CH value, that means it's a check, and
that
check could be for one or several of the parameters in columns E to XX,
now
the data in the main spreadsheet is arranged horizontally, but in order to
present it to my client, the parameters have to be presented vertically.
The
value I need to find is the 010101-XX-CH right after the first
010101-XX-CH
so I can use VLOOKUP to get the numeric data out of the main spreadsheet.
If
it can't be done I may need to change the format of column B to numbers,
but
that creates a whole different set of unforseen problems.

"Ragdyer" wrote:

Try this *array* formula:


=IF(COUNTIF(C$1:C$7,"-CH")=ROWS($1:1),INDEX(E$1:E$7,SMALL(IF(C$1:C$7= "-CH",
ROW($1:$7)),ROW(1:1))),"")

--
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 far as you anticipate there will be
returns.

You could reference a cell that contains your lookup value ("-CH", "-V",
....etc.)
so that you could change the value without having to change the formula,
say
F1:


=IF(COUNTIF(C$1:C$7,$F$1)=ROWS($1:1),INDEX(E$1:E$ 7,SMALL(IF(C$1:C$7=$F$1,RO
W($1:$7)),ROW(1:1))),"")
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Clueless" wrote in message
...
Let us say I have 5 columns:
A / B / C / D / E
010101 / 01 / -V / 010101-01-V / 5
010101 / 02 / -V / 010101-02-V / 7
010101 / 07 / -CH / 010101-07-CH / 6.3
010101 / 03 / -V / 010101-03-V / 2.1
010101 / 12 / -CH / 010101-01-CH / 9.1
010101 / 04 / -V / 010101-04-V / 1215
010101 / 69 / -CH / 010101-69-CH / 32

And so on, for many many rows. Now, let us say the first 4 (A to D)
columns
are text, and column 4 (D) is a string of columns 1, 2 & 3. As you can
see,
the codes ending in CH are not consecutive numerically, nor next to
each
other. My problem is this, I can not know what is the next -CH value
after
the first without copying down a formula for at least two columns. I
mean,
the first one I can find with no problem using
VLOOKUP("-CH",C1:D7,2,false).
Now, I would need to, based on the first -CH value (or any other
means)
determine what the second is, and then the third based on the second
and
so
on, to use a VLOOKUP formula to get at the values on column 5 (E).

Now the problem is compounded by the fact that I can not use filters
or
any
other fancy methods ;) because some of the ppl that are going to be
using
the
workbook hardly know how to open the file. Sorry for the lengthy
explanation,
but I believe it was necessary in order to understand the problem.

Thanks in advance for any help you can give me, and if you can't,
well,
you
have helped me plenty in the past, so thanks anyway :D.

Regards,

Joe