SOLVED

# INDEX, OFFSET and DGET

Occasional Contributor

# 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,

Also, Col-B, I did manually for the criteria mentioned , can this be done using some IFS formula?

Many Thanks !

Br,

Anupam

best response confirmed by anupambit1797 (Occasional Contributor)
Solution

# Re: INDEX, OFFSET and DGET

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)