Hi,
=INDEX(OFFSET(Data!C:C,0,IF(MOD(ROW(),2)=1,2,0)),MATCH(ROUND(ROW()/2,0)-6,Data!B:B,FALSE),1)
This is in column E and it refers to either column C or E on the Data tab, i.e. a choice of two columns. Since the formula needs to be consistent as it's copied down it has to alternate between finding one or other of the two columns C or E. We do this by using Modulus 2 and applying it to the row number. This is the bit of the formula
i.e. it takes the row number, say row 13 and applies Modulo 2 (i.e. it divides 13 by 2 and returns the remainder 1) The same formula on row 14 will return zero.
This MOD formula is wrapped inside an IF() function so that when the MOD formula equals 1, the IF function gives 2, and if MOD() is NOT 1 IF() gives 0. The point about 2 & 0 is that we can use these as column Offsets from Data column C. i.e. a column Offset of zero from col C us still C, and an Offset of 2 is column E.
This is where the
bit comes in. Offset takes either 3 or 5 arguments. Here we use 3 arguments. The anchor range column C, and then a row and column offset. Here the ,0 is a row offset of zero, and the column Offset is either 0 or 2 from the IF(MOD()) formula.
This OFFSET formula now forms the first part of an INDEX() function. INDEX takes three terms. A Range, a row in that range, a column in that range. So here the Index Range is either Data column C or E depending on what the Offset returns. The final bit is the MATCH() function which tells us the row number in the Index Range. MATCH() has three elements, a value, a range, and a parameter which dictates whether an EXACT match needs to be found or a value which is nearest to the required value.
ROUND(ROW()/2,0) gives the value to be matched. If this is on row 13, then 13/2 when rounded gives 7. On row 14, 14/2 also gives 7. On row 15, 15/2 rounded gives 2. So you can see that every two rows will be the same and successive groups of 2 rows will increment by 1. Since we start on row 13 which results in 7, when we deduct the constant 6 then we get the result 1. on row15 we get 2, on row 17, 3 etc...) SO now we have the series 1,2,3, which are used as the values to match in data column B which are also numbered 1,2,3. So a MATCH function on row 13 of the journal sheet will look for value 1 in column B on the Data sheet and return the value 8 since that's the row that has the value 1.
So now we have the second part of the Index() function which is the row element, the last part of the Index() function is the constant 1. So now the whole of the INDEX() function gives us either Data Column C or E, row 8, column 1.
Hope that helps. The shorter formula is similar and uses the same technique so hopefully you can apply the above explanation to it.
LinkBack URL
About LinkBacks
Register To Reply
below the post.
Bookmarks