# Using IF/And in a formula to find a value

G

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

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

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

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

=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