software

I have two tabs with a large amount of data in each.
They are called quot;5-8 ICASquot; and quot;5-15 ICASquot;.
The header row on both sheets are exactly the same.
Due to fear of macros here at work, I won't be able to use one for
this.

What I want to do is have a formula in cell T2 on sheet quot;5-15 ICASquot;
that will find the row on sheet quot;5-8 ICASquot; where cells A2, D2, R2 are
the same, and then return the value in Column L
There will only be one row on sheet quot;5-8 ICASquot; that will meet all 3 of
those conditions so there is no worry of duplicates.

Example:

Sheet - 5-15 ICAS
A2=1
D2=2
R2=3
T2=Cell that the Formula will be in

Sheet - 5-8 ICAS
A D L R
-----------------------
4 4 8 6
3 2 1 3
1 2 9 3
7 2 1 3

In this instance, the result that would be returned in T2 is quot;9quot; since
all 3 conditions are met.

I hope it can be done and that I've made it clear.

Thanks for your help.You want to use a feature called quot;Array Formulasquot; - there is very little
documentation on these and the result must be numeric. I'm not sure I will
have time to send you a sample but if I can, I will otherwise you can start
by looking at this websites:

office.microsoft.com/en-us/as...872901033.aspx

Array Formulas are great in that they allow you to have MANY conditions met
where as something like SUMIF is limited to a single condition.

Hope this helps.
gt; wrote in message oups.com...
gt;I have two tabs with a large amount of data in each.
gt; They are called quot;5-8 ICASquot; and quot;5-15 ICASquot;.
gt; The header row on both sheets are exactly the same.
gt; Due to fear of macros here at work, I won't be able to use one for
gt; this.
gt;
gt; What I want to do is have a formula in cell T2 on sheet quot;5-15 ICASquot;
gt; that will find the row on sheet quot;5-8 ICASquot; where cells A2, D2, R2 are
gt; the same, and then return the value in Column L
gt; There will only be one row on sheet quot;5-8 ICASquot; that will meet all 3 of
gt; those conditions so there is no worry of duplicates.
gt;
gt; Example:
gt;
gt; Sheet - 5-15 ICAS
gt; A2=1
gt; D2=2
gt; R2=3
gt; T2=Cell that the Formula will be in
gt;
gt; Sheet - 5-8 ICAS
gt; A D L R
gt; -----------------------
gt; 4 4 8 6
gt; 3 2 1 3
gt; 1 2 9 3
gt; 7 2 1 3
gt;
gt; In this instance, the result that would be returned in T2 is quot;9quot; since
gt; all 3 conditions are met.
gt;
gt; I hope it can be done and that I've made it clear.
gt;
gt; Thanks for your help.
gt;
Check out
www.emailoffice.com/excel/arrays-bobumlas.html
for a detailed explanation of Array-formulas

quot;Larry Squot; wrote:

gt; You want to use a feature called quot;Array Formulasquot; - there is very little
gt; documentation on these and the result must be numeric. I'm not sure I will
gt; have time to send you a sample but if I can, I will otherwise you can start
gt; by looking at this websites:
gt;
gt; office.microsoft.com/en-us/as...872901033.aspx
gt;
gt; Array Formulas are great in that they allow you to have MANY conditions met
gt; where as something like SUMIF is limited to a single condition.
gt;
gt; Hope this helps.
gt;
gt;
gt;
gt; gt; wrote in message
gt; oups.com...
gt; gt;I have two tabs with a large amount of data in each.
gt; gt; They are called quot;5-8 ICASquot; and quot;5-15 ICASquot;.
gt; gt; The header row on both sheets are exactly the same.
gt; gt; Due to fear of macros here at work, I won't be able to use one for
gt; gt; this.
gt; gt;
gt; gt; What I want to do is have a formula in cell T2 on sheet quot;5-15 ICASquot;
gt; gt; that will find the row on sheet quot;5-8 ICASquot; where cells A2, D2, R2 are
gt; gt; the same, and then return the value in Column L
gt; gt; There will only be one row on sheet quot;5-8 ICASquot; that will meet all 3 of
gt; gt; those conditions so there is no worry of duplicates.
gt; gt;
gt; gt; Example:
gt; gt;
gt; gt; Sheet - 5-15 ICAS
gt; gt; A2=1
gt; gt; D2=2
gt; gt; R2=3
gt; gt; T2=Cell that the Formula will be in
gt; gt;
gt; gt; Sheet - 5-8 ICAS
gt; gt; A D L R
gt; gt; -----------------------
gt; gt; 4 4 8 6
gt; gt; 3 2 1 3
gt; gt; 1 2 9 3
gt; gt; 7 2 1 3
gt; gt;
gt; gt; In this instance, the result that would be returned in T2 is quot;9quot; since
gt; gt; all 3 conditions are met.
gt; gt;
gt; gt; I hope it can be done and that I've made it clear.
gt; gt;
gt; gt; Thanks for your help.
gt; gt;
gt;
gt;
gt;

If I understood your problem correctly, the enclosed file solved your
problem. A couple notes on the Array Formulas:

1 Once you have completed the formula, you must quot;turn it onquot; by holding down
the CTRL-Shift and then Enter key - you will see brackets get placed on
around the formula.

2. The range must of the array must be finite - you can't select the column
heading (Example: A:H), rather it must be like A1:H45.

3. As stated earlier, the result MUST be numeric.

This should do it for you.quot;Bob Umlas, Excel MVPquot; gt; wrote in
message ...
gt; Check out
gt; www.emailoffice.com/excel/arrays-bobumlas.html
gt; for a detailed explanation of Array-formulas
gt;
gt; quot;Larry Squot; wrote:
gt;
gt;gt; You want to use a feature called quot;Array Formulasquot; - there is very little
gt;gt; documentation on these and the result must be numeric. I'm not sure I
gt;gt; will
gt;gt; have time to send you a sample but if I can, I will otherwise you can
gt;gt; start
gt;gt; by looking at this websites:
gt;gt;
gt;gt; office.microsoft.com/en-us/as...872901033.aspx
gt;gt;
gt;gt; Array Formulas are great in that they allow you to have MANY conditions
gt;gt; met
gt;gt; where as something like SUMIF is limited to a single condition.
gt;gt;
gt;gt; Hope this helps.
gt;gt;
gt;gt;
gt;gt;
gt;gt; gt; wrote in message
gt;gt; oups.com...
gt;gt; gt;I have two tabs with a large amount of data in each.
gt;gt; gt; They are called quot;5-8 ICASquot; and quot;5-15 ICASquot;.
gt;gt; gt; The header row on both sheets are exactly the same.
gt;gt; gt; Due to fear of macros here at work, I won't be able to use one for
gt;gt; gt; this.
gt;gt; gt;
gt;gt; gt; What I want to do is have a formula in cell T2 on sheet quot;5-15 ICASquot;
gt;gt; gt; that will find the row on sheet quot;5-8 ICASquot; where cells A2, D2, R2 are
gt;gt; gt; the same, and then return the value in Column L
gt;gt; gt; There will only be one row on sheet quot;5-8 ICASquot; that will meet all 3 of
gt;gt; gt; those conditions so there is no worry of duplicates.
gt;gt; gt;
gt;gt; gt; Example:
gt;gt; gt;
gt;gt; gt; Sheet - 5-15 ICAS
gt;gt; gt; A2=1
gt;gt; gt; D2=2
gt;gt; gt; R2=3
gt;gt; gt; T2=Cell that the Formula will be in
gt;gt; gt;
gt;gt; gt; Sheet - 5-8 ICAS
gt;gt; gt; A D L R
gt;gt; gt; -----------------------
gt;gt; gt; 4 4 8 6
gt;gt; gt; 3 2 1 3
gt;gt; gt; 1 2 9 3
gt;gt; gt; 7 2 1 3
gt;gt; gt;
gt;gt; gt; In this instance, the result that would be returned in T2 is quot;9quot; since
gt;gt; gt; all 3 conditions are met.
gt;gt; gt;
gt;gt; gt; I hope it can be done and that I've made it clear.
gt;gt; gt;
gt;gt; gt; Thanks for your help.
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;Ok Bob, based on the article you posted here's what I tried.

=INDEX('5-8 ICAS'!L:L,MATCH(A2amp;D2amp;R2,'5-8 ICAS'!$A$2:$A$3000amp;'5-8
ICAS'!$E$2:$E$3000amp;'5-8 ICAS'!$R$2:$R$3000,0))
I'm getting the quot;#N/Aquot; error message on that formula. Can you see
anything that needs changed?

Thanks.Saved from a few previous posts:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))wrote:
gt;
gt; I have two tabs with a large amount of data in each.
gt; They are called quot;5-8 ICASquot; and quot;5-15 ICASquot;.
gt; The header row on both sheets are exactly the same.
gt; Due to fear of macros here at work, I won't be able to use one for
gt; this.
gt;
gt; What I want to do is have a formula in cell T2 on sheet quot;5-15 ICASquot;
gt; that will find the row on sheet quot;5-8 ICASquot; where cells A2, D2, R2 are
gt; the same, and then return the value in Column L
gt; There will only be one row on sheet quot;5-8 ICASquot; that will meet all 3 of
gt; those conditions so there is no worry of duplicates.
gt;
gt; Example:
gt;
gt; Sheet - 5-15 ICAS
gt; A2=1
gt; D2=2
gt; R2=3
gt; T2=Cell that the Formula will be in
gt;
gt; Sheet - 5-8 ICAS
gt; A D L R
gt; -----------------------
gt; 4 4 8 6
gt; 3 2 1 3
gt; 1 2 9 3
gt; 7 2 1 3
gt;
gt; In this instance, the result that would be returned in T2 is quot;9quot; since
gt; all 3 conditions are met.
gt;
gt; I hope it can be done and that I've made it clear.
gt;
gt; Thanks for your help.

--

Dave Peterson

Besides entering it as an array entered forumla (cntl-shift-enter) which
someone already mentioned, I believe you also want to adjust the range that
index is referring to. As I read it now, it is using all of column L
(meaning it would start on row 1), but the match you are performing starts in
row2. Why not make the range comparable to what you are looking up, ie,
L2:L3000
--
Kevin Vaughnquot; wrote:

gt; Ok Bob, based on the article you posted here's what I tried.
gt;
gt; =INDEX('5-8 ICAS'!L:L,MATCH(A2amp;D2amp;R2,'5-8 ICAS'!$A$2:$A$3000amp;'5-8
gt; ICAS'!$E$2:$E$3000amp;'5-8 ICAS'!$R$2:$R$3000,0))
gt; I'm getting the quot;#N/Aquot; error message on that formula. Can you see
gt; anything that needs changed?
gt;
gt; Thanks.
gt;
gt;