Excel Array Question

  • Thread starter Thread starter amyacc
  • Start date Start date
A

amyacc

At a new job, there are multiple documents with "supposedly" simila
info that needs updating often. I'd like to create a master tha
populates like fields in other documents.

Do you know if i can use an excel array formula to basically transpos
text? i.e.: If the title in column A = "X" and the partner in colum
B = "Y" then take the details text from column C and paste it here.

Possible?

Please and thank you
 
=INDEX(Sheet1!$C$1:$C$300,SMALL(IF((Sheet1!$A$1:$A$300="X")*(Sheet1!$B$1:$B$
300="Y"),ROW(1:300)),1))

Enter with Ctrl+Shift+Enter rather than Enter.

This assumes there will only be one row that has X in A and Y in B.
 
Hi Tom.

Thanks for your help. I tried using that formula and something OD
happened. It should have pulled the data from cell I8 to paste, thi
cell matched the criteria of if "X"&"Y". Instead, it pulled the dat
from cell I13 that did match "X" but not "Y". At first glance ther
doesn't seem to be any reason why that data should have been pulled.

Thoughts?

THANKS
 
=INDEX('[Developmen
Update.xls]MASTER'!$I$6:$I$25,SMALL(IF(('[Developmen
Update.xls]MASTER'!$A$6:$A$25="X")*('[Developmen
Update.xls]MASTER'!$G$6:$G$25="Y"),ROW(6:25)),1))

Here's the formula. Again, the answer it's pulling does not only no
contain "Y", that cell in that row is blank. It's not the only cell i
that column that is blank. There really seems to be no reasonin
behind this.

THANKS
 
you shouldn't use row(6:25). It is used to establish an offset into the
index range - so you should keep it as Row(1:19)
 
Hi
Tom used the ROW(1:300) as parameter for the SMALL function (cycling
from 1 to 300). So getting the following values:
SMALL(....,1)
SMALL(....,2)
....
SMALL(....,300)

if you change this you don't start with the smalles but in your case
with the 6th smallest
 
Back
Top