I am trying to derive a formula to return text or Row-Column addresses
with possibly more than one true instance and I am having trouble
getting it to work. I tried using the sumproduct function but it does
not return text. To fix this I added a column with nothing but numbers
to return them but when the function finds more than one instance where
it meets the requirements it sums the values (as it should) but I need a
formula to return both values seperately. Currently this is how my
formula reads.
A[Y]=SUMPRODUCT(--(D[Y]=Sheet1!D2:D2000),--(E[Y]=Sheet1!E2:E2000),Sheet1!A2:A2000)
where [Y] denotes the particular row in which the formula is entered. I
do not care if the formula that I need returns text or the Row-Column
address where each value is true. Any direction would be much
appreciated. Thanks.


--
jdurrmsu
------------------------------------------------------------------------
jdurrmsu's Profile: http://www.excelforum.com/member.php...o&userid=27122
View this thread: http://www.excelforum.com/showthread...hreadid=507502

Assume your data (text or numbers) is in column A from A1 to A30. Use
cell C1 to enter the value you are searching for. Enter this formula in
B2:

=MATCH(C$1,A$1:A30,0)

and in B2:

=MATCH(C$1,INDIRECT("A"&(1+B1)&":A30"),0)+B1

then copy this one down for a few cells, say to row 9.

This will return the row that each item specified in C1 appears and
#N/A when the list is exhausted - no error checking built in. You can
add the following in cell D1:

="A"&B1

to get the cell address.

Hope this helps.

Pete

Sorry, the first B2 should be B1 (it's getting late!)

Pete

Another interp and a play to extract it using non-array formulas

In Sheet1,

Put in F2:
=IF(OR(D2="",E2=""),"",IF((D2=Sheet3!$A$1)*(E2=She et3!$A$2),ROW(),""))
Copy F2 down to F2000
(Leave F1 empty)

Then in Sheet3,

Enter in A1, the value of interest in Sheet1's col D
Enter in A2, the value of interest in Sheet1's col E

Put in A3:
=IF(ISERROR(SMALL(Sheet1!F:F,ROW(A1))),"",
INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!F:F,ROW(A1)),S heet1!F:F,0)))

Copy A3 down as far as required to cover the max expected returns
for any set of inputs in A1:A2. If we expect the max returns
to be say, 20 lines, then just copy A3 down to A22

A3:A22 will return all the values from Sheet1's col A
for which the inputs in A1:A2 match what's within Sheet1's cols D & E
(Results will be neatly bunched at the top)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jdurrmsu" wrote in
message ...

I am trying to derive a formula to return text or Row-Column addresses
with possibly more than one true instance and I am having trouble
getting it to work. I tried using the sumproduct function but it does
not return text. To fix this I added a column with nothing but numbers
to return them but when the function finds more than one instance where
it meets the requirements it sums the values (as it should) but I need a
formula to return both values seperately. Currently this is how my
formula reads.

A[Y]=SUMPRODUCT(--(D[Y]=Sheet1!D2:D2000),--(E[Y]=Sheet1!E2:E2000),Sheet1!A2:
A2000)
where [Y] denotes the particular row in which the formula is entered. I
do not care if the formula that I need returns text or the Row-Column
address where each value is true. Any direction would be much
appreciated. Thanks.


--
jdurrmsu
------------------------------------------------------------------------
jdurrmsu's Profile:
http://www.excelforum.com/member.php...o&userid=27122
View this thread: http://www.excelforum.com/showthread...hreadid=507502