I am having trouble coming up with a formula that I need, I'm seeking help to see if one of you guys at help me with. I have attached a screen shot of the table and the "worded" formula.

in another sheet I want to pick up the data from the table below with the Tom's ending balance of Column B in one cell and Column C in another Jeff's Ending balance of Column B in one cell and Column C in another

"I've used the =INDEX(B1:B10,MATCH($D$1,$A$1:$A$10,0)+3)" in E1 and it worked for TOM but it doesn't work for JEFF. Jeff is what I need help with but it will need to work for TOM as well.

the ending balance line will not always be 3 row under the person's name and the row between each person are not always 4 row under. I tried offset function but it always need a reference point which can't be used in this situation.


       A             B      C      D      E
1     TOM                          TOM    13
2     beg bal         11     200 
3     act             2      20 
4     end bal         13     220
5     JEFF
6     beg bal         25     154
7     ---------------------------------------
8     act             100     45
9     ---------------------------------------
10    end bal         125     200

Update for different spaces and places for End Balance and other

=INDEX($A$1:$D$10, SMALL(IF("end bal"=$A$1:$A$10, ROW($A$1:$A$10),""),ROW(1:1)), COLUMN(B1))
Just press Ctrl + Shift+ Enter at the same time after writing the formula (array formula)

A1:F10 is the whole Data wherever it start
A column for companies and Balance
B column for Cost
C column for Dividend

D column for Tom ... where you want the result
Start with the first (Tom, JEFF...) you want End Balance for like in the initial Data (You can copy Column A and remove duplicates and the non wanted data)
For example in D1 write Tom, D2 write JEFF....
In E1 write the formula and you can drag it in the same Row and down in the same column
It find the smallest row (end bal) Tom(small,1) JEFF(small,2)...

Different Order for JEFF, TOM...

In case the order is not the same as the initial Data
Create a helper column in D for example and write in it the following:
=1+COUNTIF($A$1:A2,"end bal") in D2 and drag it down it will write a number at the same row of the name (this number instead of row(1:1) in small
The new formula will become:
=INDEX($A$1:$D$10,SMALL(IF("end bal"=$A$1:$A$10, ROW($A$1:$A$10),""),VLOOKUP(E2,$A$1:$F$23,4,FALSE)), COLUMN(B1))
Array Formula press Ctrl + Shift+ Enter same time
Vlookup will find the number created by the countif that correspond to the name in E2
4 is the column D where the number is written

  • Kindly accept the answer by clicking the check mark – yass Feb 28 '17 at 21:52
  • Hello Yass, unfortunately I don't want to use B6 as a fix reference therefore the Offset function can't be use in this case. I'll post the revision of question below can you help address? many thanks. – Tue Le Mar 1 '17 at 16:26
  • I updated the formula to correspond with your information, try to write the companies in the same order as it is in the initial Data – yass Mar 1 '17 at 20:03
  • I am not able to follow your direction, I know it's my fault for changing the table for you but is it possible if you can work with the take provided above instead of the previous picture I linked? also I tried to break down the formula and I got an error in this part ---=IF("Ending Balance"=B1:B17,ROW(B1:B17)," ")---- – Tue Le Mar 1 '17 at 21:25
  • I will try to make it for the new data just follow the same you cannot break it is array formula – yass Mar 1 '17 at 21:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.