A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

formula help



 
 
Thread Tools Display Modes
  #1  
Old February 17th, 2010, 03:57 AM posted to microsoft.public.excel.newusers
ROHIT
external usenet poster
 
Posts: 36
Default 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  
Old February 17th, 2010, 02:15 PM posted to microsoft.public.excel.newusers
Max
external usenet poster
 
Posts: 8,574
Default 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  
Old February 18th, 2010, 05:43 AM posted to microsoft.public.excel.newusers
ROHIT
external usenet poster
 
Posts: 36
Default 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  
Old February 18th, 2010, 02:55 PM posted to microsoft.public.excel.newusers
Max
external usenet poster
 
Posts: 8,574
Default 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  
Old February 18th, 2010, 11:38 PM posted to microsoft.public.excel.newusers
ROHIT
external usenet poster
 
Posts: 36
Default 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  
Old February 18th, 2010, 11:54 PM posted to microsoft.public.excel.newusers
Max
external usenet poster
 
Posts: 8,574
Default 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.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.