Using MATCH and INDIRECT together

What I am trying to accomplish, is to get an email address from sheet two. Sheet two changes at least every 6 weeks. I am trying to get the address of those that speak Spanish. There is a column that designates weather they speak English or Spanish. This occurs on column Q. Their email address is located in column L.

I almost have it figured out doing this, but obviously i am doing something wrong.

I am trying to use the indirect function like this

'Sheet2!'INDIRECT("Q"&B2:$Q$300)

=MATCH("Spanish Eld",'IMOS-RE'!INDIRECT("Q"&B2:$Q$300),0)+B2

B2 is on Sheet1 I am trying to use one formula that I can easily drag down and it will not duplicate any of the emails that I already have.

-Thanks for any help that you can provide.

To do what you want, I am not sure that you want the INDIRECT function.

You want to extract all the items in column L that have "Spanish Eld" in column Q?

if so,

in the IMOS-RE sheet, in a new column, starting at row 2, enter


=IF(Q2="Spanish Eld",count(X$1:X1)+1,"") where X2 is the cell you are putting this formula in.

and copy it down.

in another cell in another colum put =MAX(X:X) again, where X is the column you entered the previous formula...

Now back in your results sheet, enter

=IF(ROWS($A$1:$A1)>'IMOS-RE'!$Y$2,"",INDEX('IMOS-RE'!L:L,MATCH(ROWS($A$1:$A1),$X:$X,0)))

where 'IMOS-RE'!Y2 contains the MAX formula and X:X is again the column you put the counting formula in.

Copy this formula down... you should get all items from column L that have that "Spanish Eld" string in the same row within column Q.

Hope I guessed right. If not, please advise.

This does work. Thank you for your time. However if there is a way to put the three formulas into one cell that would be great.

there was one honest mistake in the code to display the emails

=IF(ROWS($A$2:$A2)>'IMOS-RE'!$AC$2,"",INDEX('IMOS-RE'!L:L,MATCH(ROWS($A$2:$A2),'IMOS-RE'!$AA:$AA,0)))

it was this

=IF(ROWS($B$2:$B2)>MAX(G:G),"",INDEX('IMOS-RE'!$L:$L,MATCH(ROWS($B$2:$B2),$G:$G,0)))&";"

After Some more playing around I was able to get is all on one sheet and that will do great. that you so much!!

Here is what it looks like.

=IF(ROWS($F$2:$F2)>MAX(H:H),"",INDEX('IMOS-RE'!$L:$L,MATCH(ROWS($F$2:$F2),$G:$G,0)))

I moved the Functions that where in IMOS-RE to the other Sheet.

Again thank you so much

There is, but it might be a resource intense formula...

Try:

Please Login or Register  to view this content.

adjust ranges to suit (note: It is highly recommended that you limit the range to as small a defined range as possible...if you use whole column, you may crash).

Then confirm the formula with CTRL+SHIFT+ENTER and not just ENTER and copy down.