Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using IF/And in a formula to find a value
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,Sheet 1!$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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using IF/And in a formula to find a value
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 "lars1028" wrote: 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,Sheet 1!$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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using IF/And in a formula to find a value
IF?? I understand, in cell J2
=if(and(a2=2,b2=0,c2=1),20,"something else") -- Don Guillett SalesAid Software "lars1028" wrote in message ... 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,Sheet 1!$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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using IF/And in a formula to find a value
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 "lars1028" wrote in message ... 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,Sheet 1!$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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using IF/And in a formula to find a value
=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 "Toppers" wrote in message ... 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 "lars1028" wrote: 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,Sheet 1!$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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
find and paste formula | Excel Worksheet Functions | |||
Find Formula Problem | Excel Worksheet Functions | |||
copy and pasting a find all list into another column | Excel Discussion (Misc queries) | |||
FIND formula that searches from right? | Excel Worksheet Functions |