I have two tabs with a large amount of data in each.
They are called "5-8 ICAS" and "5-15 ICAS".
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 "5-15 ICAS"
that will find the row on sheet "5-8 ICAS" where cells A2, D2, R2 are
the same, and then return the value in Column L
There will only be one row on sheet "5-8 ICAS" 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 "9" 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 "Array Formulas" - 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:

http://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.



wrote in message
oups.com...
I have two tabs with a large amount of data in each.
They are called "5-8 ICAS" and "5-15 ICAS".
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 "5-15 ICAS"
that will find the row on sheet "5-8 ICAS" where cells A2, D2, R2 are
the same, and then return the value in Column L
There will only be one row on sheet "5-8 ICAS" 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 "9" since
all 3 conditions are met.

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

Thanks for your help.

Check out
http://www.emailoffice.com/excel/arrays-bobumlas.html
for a detailed explanation of Array-formulas

"Larry S" wrote:

You want to use a feature called "Array Formulas" - 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:

http://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.



wrote in message
oups.com...
I have two tabs with a large amount of data in each.
They are called "5-8 ICAS" and "5-15 ICAS".
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 "5-15 ICAS"
that will find the row on sheet "5-8 ICAS" where cells A2, D2, R2 are
the same, and then return the value in Column L
There will only be one row on sheet "5-8 ICAS" 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 "9" since
all 3 conditions are met.

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

Thanks for your help.

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 "turn it on" 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.


"Bob Umlas, Excel MVP" wrote in
message ...
Check out
http://www.emailoffice.com/excel/arrays-bobumlas.html
for a detailed explanation of Array-formulas

"Larry S" wrote:

You want to use a feature called "Array Formulas" - 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:

http://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.



wrote in message
oups.com...
I have two tabs with a large amount of data in each.
They are called "5-8 ICAS" and "5-15 ICAS".
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 "5-15 ICAS"
that will find the row on sheet "5-8 ICAS" where cells A2, D2, R2 are
the same, and then return the value in Column L
There will only be one row on sheet "5-8 ICAS" 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 "9" since
all 3 conditions are met.

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

Thanks for your help.

Ok Bob, based on the article you posted here's what I tried.

=INDEX('5-8 ICAS'!L:L,MATCH(A2&D2&R2,'5-8 ICAS'!$A$2:$A$3000&'5-8
ICAS'!$E$2:$E$3000&'5-8 ICAS'!$R$2:$R$3000,0))
I'm getting the "#N/A" 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:

I have two tabs with a large amount of data in each.
They are called "5-8 ICAS" and "5-15 ICAS".
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 "5-15 ICAS"
that will find the row on sheet "5-8 ICAS" where cells A2, D2, R2 are
the same, and then return the value in Column L
There will only be one row on sheet "5-8 ICAS" 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 "9" since
all 3 conditions are met.

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

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 Vaughn


" wrote:

Ok Bob, based on the article you posted here's what I tried.

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

Thanks.