Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
formula help
Hi
i got this formula =INDEX($A$13:$BD$1000,MATCH($C$13,$A$13:$A$1000,0) ,MATCH(D2,$A$11:$BZ$11,0)) i want to use the same formual but this time to match more than cell. thanks |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
formula help
Your index/match formula will return one intersection point, ie the value
where the row match & column match intersects. Maybe try re-explaining with a small-sized sample data (what you have), and what you want to happen, take us through your logic, show us the expected results based on that sample data. -- Max Singapore --- "Rohit" wrote: i got this formula =INDEX($A$13:$BD$1000,MATCH($C$13,$A$13:$A$1000,0) ,MATCH(D2,$A$11:$BZ$11,0)) i want to use the same formual but this time to match more than cell. thanks |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
formula help
so i want match from $C$13:$C$15 and return value as agregated. eg if it
match C13 where is $100, C14=$100 and C15=$100, therefore the return value should be $300. basically i want to sum the match cells (C13:C15) hope it help "Max" wrote: Your index/match formula will return one intersection point, ie the value where the row match & column match intersects. Maybe try re-explaining with a small-sized sample data (what you have), and what you want to happen, take us through your logic, show us the expected results based on that sample data. -- Max Singapore --- "Rohit" wrote: i got this formula =INDEX($A$13:$BD$1000,MATCH($C$13,$A$13:$A$1000,0) ,MATCH(D2,$A$11:$BZ$11,0)) i want to use the same formual but this time to match more than cell. thanks |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
formula help
That description wasn't really much to go by ... anyway here's a venture
hazarded via SUMPRODUCT and OFFSET which tries to align closely with/use the same "ranges" depicted in your original index/match: =SUMPRODUCT(--(A13:A1000=C13),OFFSET(A13:A1000,,MATCH(D2,A11:BZ1 1,0)-1)) Above caters for multiple matches for C13 within A13:A1000 (these can be scattered matches anywhere within A13:A100), and presumes that you want to sum up the corresponding cells in the column range specified by where D2 is found in the range A11:BZ11 (the OFFSET bit grabs this column). Bullseye or 9.9/10? celebrate it, hit the YES below. Let me know -- Max Singapore --- "Rohit" wrote: so i want match from $C$13:$C$15 and return value as agregated. eg if it match C13 where is $100, C14=$100 and C15=$100, therefore the return value should be $300. basically i want to sum the match cells (C13:C15) hope it help |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
formula help
i want this to add from C13 TO C15 to have a return value $300. becoz i want
the formula to match 3 cells. "Max" wrote: That description wasn't really much to go by ... anyway here's a venture hazarded via SUMPRODUCT and OFFSET which tries to align closely with/use the same "ranges" depicted in your original index/match: =SUMPRODUCT(--(A13:A1000=C13),OFFSET(A13:A1000,,MATCH(D2,A11:BZ1 1,0)-1)) Above caters for multiple matches for C13 within A13:A1000 (these can be scattered matches anywhere within A13:A100), and presumes that you want to sum up the corresponding cells in the column range specified by where D2 is found in the range A11:BZ11 (the OFFSET bit grabs this column). Bullseye or 9.9/10? celebrate it, hit the YES below. Let me know -- Max Singapore --- "Rohit" wrote: so i want match from $C$13:$C$15 and return value as agregated. eg if it match C13 where is $100, C14=$100 and C15=$100, therefore the return value should be $300. basically i want to sum the match cells (C13:C15) hope it help |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
formula help
Sorry, I'm out of further guesses for you. Maybe someone else will have a
go. Suggest you start a new thread, describe it fully there with sample data -- Max Singapore "Rohit" wrote in message ... i want this to add from C13 TO C15 to have a return value $300. becoz i want the formula to match 3 cells. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|