Finance

Contents

• 1 Question
• 2 Question

QuestionEdit

QUESTION:

thanks for the previous help i have attached the file to understand better. please go thru the same

mukesh

ANSWER: mukesh Arya

=INDEX($C:$C,MATCH($A3,$A$7:$A$30,0)+MATCH(B$2,OFFSET($A$6,MATCH($A3,$A$7:$A$30,0),0,5,1),0)+5,1)

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 ----------

QUESTION:

thanks actually its the reverse the three blocks are the input one and the topmost yellow color is the summary of three blocks

mukesh

mukesh

AnswerEdit

Mukesh,

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.

---

OK - I was wrong. After closer examination I see Bob interpeted your picture reverse of mine. (sorry to keep bothering you, but I wanted to get this right). I have used his assumptions on location in the worksheet and written a single formula that will work in all cells. Enter this formula in C10 (icici and april) and then copy and paste it down column C in the cells next to month names:

=VLOOKUP(INDIRECT("A"&ROW()-MATCH(A10,$B$2:$D$2,0)-1),$A$3:$D$5,MATCH($A10,$B$2:$D$2,0)+1,FALSE)

Sorry for my stubborness on continuing to misinterpret what you wanted.

QuestionEdit

hi bob

please go thru the attachement... i have tried to explain my queries

thanks once again for your previous support

mukesh

AnswerEdit

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