INDEX, OFFSET and DGET

[anupambit1797]

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

[#2 · Accepted solution]

@anupambit1797 

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)