Returning Multiple Text or Address Locations

[jdurrmsu]

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

 

[Pete]

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

 

[Pete]

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

Pete

 

[Max]

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=Sheet3!$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)),Sheet1!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)