Using IF/And in a formula to find a value

[Guest]

I'm trying to learn formulas but not having much luck. Here's what I have.
I have a total of 7 columns and 9 rows of numbers like this shown below.

A B C J K L M
0 0 1 10 10 10 10
0 0 1 10 10 10 10
0 0 1 10 10 10 10
2 1 0 8 8 8 8
2 1 0 8 8 8 8
2 1 0 8 8 8 8
2 0 1 20 20 20 20
2 0 1 20 20 20 20
2 0 1 20 20 20 20

Here is what I want: Whenever column A=2, B=0, and C=1 then in a cell on
sheet 2 I would like the first value in column J when this is true to show
up (which would be the number 20).
Here was my formula:
IF(AND(Sheet1!$A$1:$A$9=2,Sheet1!$B$1:$B$9=0,Sheet1!$C$1:$C$9=1),Sheet1!$J:$J9)

This formula gives an error which I can't figure out. Maybe I'm going at
this all wrong. Would appreciate any help

 

[Guest]

Enter with Ctrl+shift+enter (as an array formula)

=INDEX($J$1:$J$9,MATCH(1,($A$1:$A$9=2)*($B$1:$B$9=0)*($C$1:$C$9=1),0))

You can change the constants (2,0 and 1) to be cells holding thes values

HTH

 

[Don Guillett]

IF?? I understand, in cell J2
=if(and(a2=2,b2=0,c2=1),20,"something else")

 

[Biff]

Hi!

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(J1:J9,MATCH("201",A1:A9&B1:B9&C1:C9,0))

Biff

 

[Biff]

=INDEX($J$1:$J$9,MATCH(1,($A$1:$A$9=2)*($B$1:$B$9=0)*($C$1:$C$9=1),0))

If a cell is empty:

($B$1:$B$9=0)

Will return TRUE, so:

2.....(Empty).....1

Will match.

Biff