Returning Multiple Text or Address Locations
 

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

Returning Multiple Text or Address Locations
 

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

Returning Multiple Text or Address Locations
 

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

Pete

Returning Multiple Text or Address Locations
 

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