Forum Discussion
INDEX, OFFSET and DGET
Dear Experts,
I have a Worksheet as this:-
Now , Col-A( goes from 0-274) , Col-B( goes like this , For 0<PRB<15, RBGidx =0 , 16<PRB<31 , RBGidx =1 and so on. and Col-C, has startPrb (which is same as PRB#, but in different order),
So , I want to populate Col-D, so that against each startPrb, I have the RBGindex as shown in snap( somehow I am not sure how to attach file here), calculated using Vlookup which is an easy task,
I want to do same task using DGET/INDEX/OFFSET, in Col-E/F/G respectively, could you please help share the formula for the same.
Also, Col-B, I did manually for the criteria mentioned , can this be done using some IFS formula?
Many Thanks !
Br,
Anupam
In B2: =QUOTIENT(A2,16)
Fill down.
DGET is not a good idea since you'd need to create a separate criteria range for each value in column C.
As a simple alternative, enter =QUOTIENT(C2,16) in E2 and fill down.
In F2, you could use =INDEX(B:B,MATCH(C2,A:A,0))
And in G2: =OFFSET($B$1,MATCH(C2,A:A,0),0)
1 Reply
In B2: =QUOTIENT(A2,16)
Fill down.
DGET is not a good idea since you'd need to create a separate criteria range for each value in column C.
As a simple alternative, enter =QUOTIENT(C2,16) in E2 and fill down.
In F2, you could use =INDEX(B:B,MATCH(C2,A:A,0))
And in G2: =OFFSET($B$1,MATCH(C2,A:A,0),0)