I am trying to get values from other column, based on some logic, crossed logic, but I didn't get any result with the only thing I know: IF or VLOOKUP.
The table is in this way:
So basically there are groups divided by blank row, and in column D I want to add the value from column B, but according with other letter that the one from the row ( C is from Credit, D is from Debtor). So in first D5 I have C on the same row in A5, so I need to take value from D, which is 2, according to B6.
So sorry if this question is too easy or stupid, but I don't know much about excel formulas, other that the basic one.
CodePudding user response：
I'd say the fastest way with formula takes two columns. The first one (let's say column C) will use this formula to define the blocks:
=IF(A5="",C4 1,IFERROR(C4 0,0))
It's meant for cell C5.
The second column will give back the actual result:
=SUMIFS(B:B,A:A,IF(A5="C","D",IF(A5="D","C","")),C:C,C5)
It's meant for cell D5.
Place them accordingly and drag them down to cover your list.
Then again: if you are looking just for the number and not for its sum (and therefore assuming all numbers are equal for each letter in each block), just use this one in cell D5 instead of the previous one:
=SUMIFS(B:B,A:A,IF(A5="C","D",IF(A5="D","C","")),C:C,C5)/COUNTIFS(A:A,IF(A5="C","D",IF(A5="D","C","")),C:C,C5)
Now if you were to use only IF and VLOOKUP functions, a possible solution could be this one:
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| Block index | ||||||
| 0 | What to search | What is | Value | Result | ||
| C | 1 | =IF(A5="",C4 1,C4) | =IF(A5="",C4 1,C4)&SE(A5="C","D",SE(A5="D","C","")) | =C5&A5 | =B5 | =IF(VLOOKUP(D5,E:F,2,FALSO)=0,"",VLOOKUP(D5,E:F,2,FALSO)) |
| D | 2 | =IF(A6="",C5 1,C5) | =IF(A6="",C5 1,C5)&SE(A6="C","D",SE(A6="D","C","")) | =C6&A6 | =B6 | =IF(VLOOKUP(D6,E:F,2,FALSO)=0,"",VLOOKUP(D6,E:F,2,FALSO)) |
| D | 2 | =IF(A7="",C6 1,C6) | =IF(A7="",C6 1,C6)&SE(A7="C","D",SE(A7="D","C","")) | =C7&A7 | =B7 | =IF(VLOOKUP(D7,E:F,2,FALSO)=0,"",VLOOKUP(D7,E:F,2,FALSO)) |
| =IF(A8="",C7 1,C7) | =IF(A8="",C7 1,C7)&SE(A8="C","D",SE(A8="D","C","")) | =C8&A8 | =B8 | =IF(VLOOKUP(D8,E:F,2,FALSO)=0,"",VLOOKUP(D8,E:F,2,FALSO)) | ||
| D | 3 | =IF(A9="",C8 1,C8) | =IF(A9="",C8 1,C8)&SE(A9="C","D",SE(A9="D","C","")) | =C9&A9 | =B9 | =IF(VLOOKUP(D9,E:F,2,FALSO)=0,"",VLOOKUP(D9,E:F,2,FALSO)) |
| C | 4 | =IF(A10="",C9 1,C9) | =IF(A10="",C9 1,C9)&SE(A10="C","D",SE(A10="D","C","")) | =C10&A10 | =B10 | =IF(VLOOKUP(D10,E:F,2,FALSO)=0,"",VLOOKUP(D10,E:F,2,FALSO)) |
| =IF(A11="",C10 1,C10) | =IF(A11="",C10 1,C10)&SE(A11="C","D",SE(A11="D","C","")) | =C11&A11 | =B11 | =IF(VLOOKUP(D11,E:F,2,FALSO)=0,"",VLOOKUP(D11,E:F,2,FALSO)) | ||
| D | 2 | =IF(A12="",C11 1,C11) | =IF(A12="",C11 1,C11)&SE(A12="C","D",SE(A12="D","C","")) | =C12&A12 | =B12 | =IF(VLOOKUP(D12,E:F,2,FALSO)=0,"",VLOOKUP(D12,E:F,2,FALSO)) |
| C | 5 | =IF(A13="",C12 1,C12) | =IF(A13="",C12 1,C12)&SE(A13="C","D",SE(A13="D","C","")) | =C13&A13 | =B13 | =IF(VLOOKUP(D13,E:F,2,FALSO)=0,"",VLOOKUP(D13,E:F,2,FALSO)) |
| C | 5 | =IF(A14="",C13 1,C13) | =IF(A14="",C13 1,C13)&SE(A14="C","D",SE(A14="D","C","")) | =C14&A14 | =B14 | =IF(VLOOKUP(D14,E:F,2,FALSO)=0,"",VLOOKUP(D14,E:F,2,FALSO)) |