thanks for the previous help i have attached the file to understand better. please go thru the same
ANSWER: mukesh Arya
would be the formula in B3 if I use your picture and have "SPEND" in B1 and 100 in B3.
I then drag fill this formula down for a total of 3 rows and across for a total of 3 columns and it produces the numbers you show.
I have attached a picture laid out on a worksheet with cell addresses so you can see how it is applied. I lookup the values in column A rows 3 - 5 in the remainder of column A. Once I find that value, I use an offset from that point to lookup the month label in row 2 for the column with the formula and find that location. Then I add all these rows together to get the actual row location of the value that I need to retrieve and pass that to the index function for column C:C to return the appropriate value.
There are probably other ways to do it, but this worked for me as you can see in the image. All the numbers in my yellow portion are being returned using the formula.
---------- FOLLOW-UP ----------
thanks actually its the reverse the three blocks are the input one and the topmost yellow color is the summary of three blocks
that is exactly the way I have it. (so it isn't the reverse) In the picture the formula is in the yellow area. Not sure how I can make it any clearer for you. Sorry if you don't understand the answer. It worked fine for me.
I see you asked bob this same question and received a different formula each of 3 rows. I also noticed that his formula is from the same perspective as mine - placed in the yellow area.
If you only have 3 rows that you need to work with, I guess you can put in a different formula for each row or even put in a different formula for each cell. But I would clarify that my only my single formula is needed and it can easily be adjusted to handle more rows in the yellow area if that is your real situation. If you want to send a sample workbook, I can set it up for you or perhaps your are happy with what Bob gave you.
Sorry for my stubborness on continuing to misinterpret what you wanted.
please go thru the attachement... i have tried to explain my queries
thanks once again for your previous support
If your yellow cells are A2:D5, then enter this in C10 (april spend for icici): =OFFSET($A$1,MATCH($A$8,A:A,0)-1,MATCH(A10,$2:$2,0)) and fill down to C12. In C17 (april spend for cabr): =OFFSET($A$1,MATCH($A$15,A:A,0)-1,MATCH(A17,$2:$2,0)) and fill down to C19 in C24 (april spend for ipo) =OFFSET($A$1,MATCH($A$22,A:A,0)-1,MATCH(A24,$2:$2,0)) and fill down to C26