| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple cell value
I am using a formula and it has 2 or 3 different cells, it like as follow:
cell A1: January, A2:CH now I want to take a formula to use those two cells as a combine like, Index($B4:$I9000,Match(cell("contents",$A$2),$B$4: $B$9000,0),2) --this is my original formula now I want to change and put down a two matching cells like A1 and A2 in the formula... How can I do this? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cell value
How about:
=INDEX($C4:$C9000,MATCH(1,($A$1=$A$4:$A$9000)*($A$ 2=$B$4:$B$9000),0)) 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. I'm retrieving the value from column C (instead of specifying a column in B:I) And I'm looking for the first row that has a match in A4:A9000 to A1 and at the same time a match in B4:B9000 to A2. These portions: ($A$1=$A$4:$A$9000) and ($A$2=$B$4:$B$9000) Each return a bunch of true/false--depending on the match. But when they're multiplied using: ($A$1=$A$4:$A$9000)*($A$2=$B$4:$B$9000) That bunch of true/falses becomes a bunch of 1's and 0's. (true*true = 1, false*anything = 0) IP wrote: I am using a formula and it has 2 or 3 different cells, it like as follow: cell A1: January, A2:CH now I want to take a formula to use those two cells as a combine like, Index($B4:$I9000,Match(cell("contents",$A$2),$B$4: $B$9000,0),2) --this is my original formula now I want to change and put down a two matching cells like A1 and A2 in the formula... How can I do this? -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cell value
On May 7, 6:38 pm, Dave Peterson wrote:
How about: =INDEX($C4:$C9000,MATCH(1,($A$1=$A$4:$A$9000)*($A$ 2=$B$4:$B$9000),0)) 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. I'm retrieving the value from column C (instead of specifying a column in B:I) And I'm looking for the first row that has a match in A4:A9000 to A1 and at the same time a match in B4:B9000 to A2. These portions: ($A$1=$A$4:$A$9000) and ($A$2=$B$4:$B$9000) Each return a bunch of true/false--depending on the match. But when they're multiplied using: ($A$1=$A$4:$A$9000)*($A$2=$B$4:$B$9000) That bunch of true/falses becomes a bunch of 1's and 0's. (true*true = 1, false*anything = 0) IP wrote: I am using a formula and it has 2 or 3 different cells, it like as follow: cell A1: January, A2:CH now I want to take a formula to use those two cells as a combine like, Index($B4:$I9000,Match(cell("contents",$A$2),$B$4: $B$9000,0),2) --this is my original formula now I want to change and put down a two matching cells like A1 and A2 in the formula... How can I do this? -- Dave Peterson I can try it on Monday, cause this is for my work which I am working for and I don't have a access to communicate, so I will try on Monday then I can answer you is that working or not, but thanks for answer... |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cell value
On May 10, 10:02 am, wrote:
On May 7, 6:38 pm, Dave Peterson wrote: How about: =INDEX($C4:$C9000,MATCH(1,($A$1=$A$4:$A$9000)*($A$ 2=$B$4:$B$9000),0)) 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. I'm retrieving the value from column C (instead of specifying a column in B:I) And I'm looking for the first row that has a match in A4:A9000 to A1 and at the same time a match in B4:B9000 to A2. These portions: ($A$1=$A$4:$A$9000) and ($A$2=$B$4:$B$9000) Each return a bunch of true/false--depending on the match. But when they're multiplied using: ($A$1=$A$4:$A$9000)*($A$2=$B$4:$B$9000) That bunch of true/falses becomes a bunch of 1's and 0's. (true*true = 1, false*anything = 0) IP wrote: I am using a formula and it has 2 or 3 different cells, it like as follow: cell A1: January, A2:CH now I want to take a formula to use those two cells as a combine like, Index($B4:$I9000,Match(cell("contents",$A$2),$B$4: $B$9000,0),2) --this is my original formula now I want to change and put down a two matching cells like A1 and A2 in the formula... How can I do this? -- Dave Peterson I can try it on Monday, cause this is for my work which I am working for and I don't have a access to communicate, so I will try on Monday then I can answer you is that working or not, but thanks for answer... Hi Dave, It is not working... Actually let me show you a proper way what I am doing... Here is my example: A:_______ B:_______ C:_______ 1 2 3 4 5 6 January 74 256 1260 565 and so on January 75 1560 120 .... January 76 1820 978 888 January 102 January 603 2250 5678 3345 and so on .. .. .. February 74 could be any number February 75 February 76 Feb 102 .. .. .. March 74 March 75 .. .. .. Any month Now I want to get a month (any from the list), 2 from list and 3, 4, 5 from the list... That's what I am trying to do this. If I use a same formula it is giving (my formula) me answer from one thing only (any one from A, B or C), but I want to match that with A and B or C, so I want in 2 cells which would be any A and B or C... Hope you understand my question... Thank you for taking a time... |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cell value
|
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cell value
On May 12, 7:35 pm, Dave Peterson wrote:
I don't understand. Maybe you can explain again or someone else will chime in. wrote: On May 10, 10:02 am, wrote: On May 7, 6:38 pm, Dave Peterson wrote: How about: =INDEX($C4:$C9000,MATCH(1,($A$1=$A$4:$A$9000)*($A$ 2=$B$4:$B$9000),0)) 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. I'm retrieving the value from column C (instead of specifying a column in B:I) And I'm looking for the first row that has a match in A4:A9000 to A1 and at the same time a match in B4:B9000 to A2. These portions: ($A$1=$A$4:$A$9000) and ($A$2=$B$4:$B$9000) Each return a bunch of true/false--depending on the match. But when they're multiplied using: ($A$1=$A$4:$A$9000)*($A$2=$B$4:$B$9000) That bunch of true/falses becomes a bunch of 1's and 0's. (true*true = 1, false*anything = 0) IP wrote: I am using a formula and it has 2 or 3 different cells, it like as follow: cell A1: January, A2:CH now I want to take a formula to use those two cells as a combine like, Index($B4:$I9000,Match(cell("contents",$A$2),$B$4: $B$9000,0),2) --this is my original formula now I want to change and put down a two matching cells like A1 and A2 in the formula... How can I do this? -- Dave Peterson I can try it on Monday, cause this is for my work which I am working for and I don't have a access to communicate, so I will try on Monday then I can answer you is that working or not, but thanks for answer... Hi Dave, It is not working... Actually let me show you a proper way what I am doing... Here is my example: A:_______ B:_______ C:_______ 1 2 3 4 5 6 January 74 256 1260 565 and so on January 75 1560 120 .... January 76 1820 978 888 January 102 January 603 2250 5678 3345 and so on . . . February 74 could be any number February 75 February 76 Feb 102 . . . March 74 March 75 . . . Any month Now I want to get a month (any from the list), 2 from list and 3, 4, 5 from the list... That's what I am trying to do this. If I use a same formula it is giving (my formula) me answer from one thing only (any one from A, B or C), but I want to match that with A and B or C, so I want in 2 cells which would be any A and B or C... Hope you understand my question... Thank you for taking a time... -- Dave Peterson Please check your email I emailed you my question and a example spreadsheet. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cell value
Please keep the discussion in the newsgroups.
You'll find that you have lots more potential responders. wrote: <snipped Please check your email I emailed you my question and a example spreadsheet. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple formats in a single cell with multiple formulas | Excel Worksheet Functions | |||
| Transposing Multiple Cell References to Multiple Values (NOT total | Excel Discussion (Misc queries) | |||
| Transposing Multiple Cell references as Multiple Values | Excel Discussion (Misc queries) | |||
| Display multiple lines of text within a cell from multiple column. | Excel Worksheet Functions | |||
| INTRICATE PROBLEM- How to find multiple text,excluding "H", in a multiple range of cells, then replacing 0 with another number in another cell | Excel Worksheet Functions | |||
Linear Mode
