0

I'm currently working on Excel, and I used the Index function. However, since I wanted to return value, it can't return the cell with merge cell where it is intended for what I look for or return for.

I am currently using this formula just like in the 2nd picture.

=IFERROR(INDEX('General Journal'!D6:D11,SMALL(IF('General Journal'!C6:C11=B2,ROW('General Journal'!C6:C11)-5,""),ROW('General Journal'!C6:C11)-5)),"")

At the first date, it was successful because it just read the first cell in a 2 merge cell. However, in second and third, it was returned "0" because it is a merge cell and the value was like in the second cell. What formula could I still use even though it is in a merge cell? Thank you!

2nd Picture

1st Picture

Dum Acco
  • 15
  • 5
  • Simplict would be a Helper column "General Journal" with a formula that repeats the values from the merged cells, and refer to that in your other formula – chris neilsen Nov 15 '21 at 03:21
  • @chrisneilsen hi! thank you for replying. I do apologize, but what do you mean by the simplict? – Dum Acco Nov 15 '21 at 05:34
  • Oops, spelling. Meant "Simplest" as in I can't think of a simpler way – chris neilsen Nov 15 '21 at 05:43
  • I believe that [this fix for Merge Filtering](https://stackoverflow.com/questions/49816515/excel-filtering-for-merged-cells/49817670#49817670) should also work in this situation? – Chronocidal Nov 15 '21 at 12:17

1 Answers1

0

FILTER function

If you are using Office365 try something like this

=FILTER(FILTER(B5:D11,D5:D11<>""),{1,0,1})
Nimantha
  • 6,405
  • 6
  • 28
  • 69
12Rev79
  • 166
  • 1
  • 6