Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pull value of a range within a range of data
I have a large range of Names in Column A. Some of the names show up
multiple times (ex: smith). Only one of the "Smith" has a unique character located in Column B. Once excel locates the unique item in Column B, is there a way to pull the single cell's data that is housed in column C? I'm thinking that sumproduct is the way to go, I'm just not sure. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pull value of a range within a range of data
Sumproduct may be the way to go if the value in column C is a number. And there
are no duplicates to worry about: =sumproduct(--(a1:a10="Smith"),--(b1:b10="x"),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 ========= But if column C contains text and you only want the first match (just like =vlookup()), ... 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)) CCrew2000 wrote: I have a large range of Names in Column A. Some of the names show up multiple times (ex: smith). Only one of the "Smith" has a unique character located in Column B. Once excel locates the unique item in Column B, is there a way to pull the single cell's data that is housed in column C? I'm thinking that sumproduct is the way to go, I'm just not sure. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pull value of a range within a range of data
Worked perfectly! Thanks!
"Dave Peterson" wrote: Sumproduct may be the way to go if the value in column C is a number. And there are no duplicates to worry about: =sumproduct(--(a1:a10="Smith"),--(b1:b10="x"),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 ========= But if column C contains text and you only want the first match (just like =vlookup()), ... 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)) CCrew2000 wrote: I have a large range of Names in Column A. Some of the names show up multiple times (ex: smith). Only one of the "Smith" has a unique character located in Column B. Once excel locates the unique item in Column B, is there a way to pull the single cell's data that is housed in column C? I'm thinking that sumproduct is the way to go, I'm just not sure. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I link data from a horizontal range to a vertical range? | Excel Worksheet Functions | |||
Using a date range in a formula to pull info to the correct column | Excel Worksheet Functions | |||
How do I get an IF statement to pull a date range?? | Excel Worksheet Functions | |||
Pull information based on a range of numbers | Excel Worksheet Functions | |||
How do I change a range name back to the underlying data range? | Excel Worksheet Functions |