I'm trying to get the value of one cell from a Data worksheet into an
specific one. The formula is as follows:
IF(AND('Data'!C5:C50003=A2,'Data'!A5:A50003=A4,'Da ta'!D5:D50003="Land"),'Data'!E5:E50003,0)

Were A2 is a country name, A4 is a date and the value to bring back is a
number.

What can I do since there is only one value in the Data spreadsheet that
would comply with all 3 conditions?

If generally works on one cell not a range. When you have a range within IF
you must make it an array formula, and logical functions like AND & OR
cannot be used within array formulas

So let's change horses.
This is my data
a x aaab 2
c y baaa 3
c z abaa 4
a k aaba 5

This formula =SUMPRODUCT(--(A1:A4="c"),--(B1:B4="z"),--(C1:C4="abaa"),D1:D4)
will find the single match and return the value 4.
The formula is NOT and array formula
Or course, it there were two rows matching the three criteria it will return
the sum of the two D values.

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
Debra Dalgleish
http://www.contextures.com/xlFunctio...tml#SumProduct

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"LuisGe" wrote in message
...
I'm trying to get the value of one cell from a Data worksheet into an
specific one. The formula is as follows:
IF(AND('Data'!C5:C50003=A2,'Data'!A5:A50003=A4,'Da ta'!D5:D50003="Land"),'Data'!E5:E50003,0)

Were A2 is a country name, A4 is a date and the value to bring back is a
number.

What can I do since there is only one value in the Data spreadsheet that
would comply with all 3 conditions?

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=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 only use the whole column in xl2007.

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))

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Or if you want to include the "router-1" in the formula:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10="router-1"),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

LuisGe wrote:

I'm trying to get the value of one cell from a Data worksheet into an
specific one. The formula is as follows:
IF(AND('Data'!C5:C50003=A2,'Data'!A5:A50003=A4,'Da ta'!D5:D50003="Land"),'Data'!E5:E50003,0)

Were A2 is a country name, A4 is a date and the value to bring back is a
number.

What can I do since there is only one value in the Data spreadsheet that
would comply with all 3 conditions?

--

Dave Peterson

Are the values in column E numeric? If so, you could use this:

=SUMPRODUCT(('Data'!C5:C50003=A2)*('Data'!A5:A5000 3=A4)*('Data'!
D5:D50003="Land"),'Da*ta'!E5:E50003)

It will actually add any values in column E that meet the criteria,
but if the criteria give rise to only one unique combination then that
single value from column E is what you will get.

Hope this helps.

Pete

On Nov 5, 1:46*pm, LuisGe wrote:
I'm trying to get the value of one cell from a Data worksheet into an
specific one. The formula is as follows:
IF(AND('Data'!C5:C50003=A2,'Data'!A5:A50003=A4,'Da ta'!D5:D50003="Land"),'Da*ta'!E5:E50003,0)

Were A2 is a country name, A4 is a date and the value to bring back is a
number.

What can I do since there is only one value in the Data spreadsheet that
would comply with all 3 conditions?

Hi,

Try this array formula, I shortened the ranges for debugging so set them
back to what you need.

=INDEX(Data!E5:E20,MATCH(1,(Data!A5:A20=A4)*(Data! D5:D20="Land")*(Data!C5:C20=A2),0))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike



"LuisGe" wrote:

I'm trying to get the value of one cell from a Data worksheet into an
specific one. The formula is as follows:
IF(AND('Data'!C5:C50003=A2,'Data'!A5:A50003=A4,'Da ta'!D5:D50003="Land"),'Data'!E5:E50003,0)

Were A2 is a country name, A4 is a date and the value to bring back is a
number.

What can I do since there is only one value in the Data spreadsheet that
would comply with all 3 conditions?

THANKS!! Worked perfectly!!

"Mike H" wrote:

Hi,

Try this array formula, I shortened the ranges for debugging so set them
back to what you need.

=INDEX(Data!E5:E20,MATCH(1,(Data!A5:A20=A4)*(Data! D5:D20="Land")*(Data!C5:C20=A2),0))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike



"LuisGe" wrote:

I'm trying to get the value of one cell from a Data worksheet into an
specific one. The formula is as follows:
IF(AND('Data'!C5:C50003=A2,'Data'!A5:A50003=A4,'Da ta'!D5:D50003="Land"),'Data'!E5:E50003,0)

Were A2 is a country name, A4 is a date and the value to bring back is a
number.

What can I do since there is only one value in the Data spreadsheet that
would comply with all 3 conditions?