Oooh, I guarantee you are going to have fun with this one.

[Clueless]

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",C17,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 .

Regards,

Joe

 

[Biff]

Hi!

Not sure which column of values you want to return, column D or E? Or both?

Anyhow, this will return the values from column E for each corresponfing
instance of "-CH".

Entered as an array using the key combination of CTRL,SHIFT,ENTER: (pay
attention to this statement! It means something!!)

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

Copy down until you get blanks.

Biff

 

[Ragdyer]

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))),"")

 

[Clueless]

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]

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?

 

[Clueless]

I would, with a vlookup function, but in order to use vlookup, I have to know
what it is I am looking for. But never mind with an answer, apparently
someone else had the same problem and came up with a solution: creating a
function all of their own, which works for me like a charm. If anyone is
interested, here is the link:

http://www.ozgrid.com/Excel/find-nth.htm

I would like to thank you all for helping me. This really is a wonderful
resource and has gotten me out of a tight spot more times than I can count.

Best regards,

Joe

 

[Ragdyer]

As a topic of discussion:

The formulas that Biff and I suggested will return *all* matches to the
search criteria, since you didn't stipulate in your post that you would be
interested in a *specific* occurrence.

However, the formulas we posted can very easily return a *specific* instance
of the criteria if desired, and can accomplish this *without adding*
additional columns containing additional formulas.
Since your data is populating out to, say Column Z, you could enter your
criteria in AA1 and the instance (occurrence) of the criteria to return in
AA2, and the Column to return of *that* occurrence in AA3, and try this
*array* formula:

=VLOOKUP(INDEX(E1:E7,SMALL(IF(C1:C7=AA1,ROW(1:7)),AA2)),E1:Z7,AA3,0)

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.

Now, since it appears that you're looking for a single formula to return a
single value, there's no error checking included, and no absolutes for
copying down.

However, this works with your *present* data set, with *no* extra columns or
formulas ... BUT ... it *is* an array formula, so you know whether or not
this will satisfy your needs, as compared to the scenario displayed in the
link you posted.

--
Regards,

RD

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

I would, with a vlookup function, but in order to use vlookup, I have to know
what it is I am looking for. But never mind with an answer, apparently
someone else had the same problem and came up with a solution: creating a
function all of their own, which works for me like a charm. If anyone is
interested, here is the link:

http://www.ozgrid.com/Excel/find-nth.htm

I would like to thank you all for helping me. This really is a wonderful
resource and has gotten me out of a tight spot more times than I can count.

Best regards,

Joe