Lookup values in other table that has a range of values

I've got two data sets from a drilling project collected by different sensors.

One table has rate of penetration at the recorded depths. The other has wireline gamma values for the same hole but at different recorded depths.

Rate of Penetration data set

holeid = [12;12;12;12;12;12;12;12;12;12;12;12;12;12;12;12];
depth = [0;0.0542;0.0801;0.2222;0.3959;0.4572;0.5110;0.5348;0.5712;0.6099;0.6437;0.6799;0.8011;0.8928;0.9590;1.0110];
rop = [118.7000;207.8000;139.6000;176.0000;177.8000;229.3000;242.4000;138.9000;85.7000;140.8000;164.5000;125.4000;189.8000;164.0000;118.4000;211.4000];
rophole12 = table(holeid,depth,rop);

Gamma data set

(e.g. depth from 0 to 0.13 is treated as from/to range and has gamma value of 1, depth of 0.13 to 0.22 has gamma value of 12, etc)

holeid1 = [12;12;12;12;12;12;12;12;12;12;12;12;12];
depth1 = [0;0.13;0.22;0.36;0.48;0.56;0.67;0.78;0.84;0.96;1.03;1.14;1.24;];
gamma1 = [1;12;34;23;42;12;43;67;111;200;153;188;55];
gammahole12 = table(holeid1,depth1,gamma1);

What's the best way to get the gamma values from gammahole12 at the depths specified in the rophole12 table e.g. gamma at 0, 0.0542, 0.0801, etc?

In excel, I've used the vlookup function with true. Is there a MATLAB equivalent or different way to lookup a value within a different table with approximate reference, e.g. depth in this case?

Answer 1

[AndresVar]

Something like this would add the gamma variable to your first table, BUT it's matching to nearest gamma

rophole12.gamma = interp1(gammahole12.depth1,gammahole12.gamma1,rophole12.depth,'nearest')

See: 1-D data interpolation (table lookup) - MATLAB interp1 (mathworks.com) for other matching options such as next or previeous

[Cris LaPierre]

Using 'previous' seems to give the same results as vlookup.

holeid = [12;12;12;12;12;12;12;12;12;12;12;12;12;12;12;12];
depth = [0;0.0542;0.0801;0.2222;0.3959;0.4572;0.5110;0.5348;0.5712;0.6099;0.6437;0.6799;0.8011;0.8928;0.9590;1.0110];
rop = [118.7000;207.8000;139.6000;176.0000;177.8000;229.3000;242.4000;138.9000;85.7000;140.8000;164.5000;125.4000;189.8000;164.0000;118.4000;211.4000];
rophole12 = table(holeid,depth,rop);

holeid1 = [12;12;12;12;12;12;12;12;12;12;12;12;12];
depth1 = [0;0.13;0.22;0.36;0.48;0.56;0.67;0.78;0.84;0.96;1.03;1.14;1.24;];
gamma1 = [1;12;34;23;42;12;43;67;111;200;153;188;55];
gammahole12 = table(holeid1,depth1,gamma1);

% Use interp1 with method set to 'previous' to get same result as vlookup
% (for this example, at least)
rophole12.gamma = interp1(gammahole12.depth1,gammahole12.gamma1,rophole12.depth,'previous')

rophole12 = 16×4 table
    holeid    depth      rop     gamma
    ______    ______    _____    _____

      12           0    118.7       1 
      12      0.0542    207.8       1 
      12      0.0801    139.6       1 
      12      0.2222      176      34 
      12      0.3959    177.8      23 
      12      0.4572    229.3      23 
      12       0.511    242.4      42 
      12      0.5348    138.9      42 
      12      0.5712     85.7      12 
      12      0.6099    140.8      12 
      12      0.6437    164.5      12 
      12      0.6799    125.4      43 
      12      0.8011    189.8      67 
      12      0.8928      164     111 
      12       0.959    118.4     111 
      12       1.011    211.4     200 

[Daniel Goldstein]

Previous works better for this purpose but good to be aware of the nearest and next options too. Thank you!

Answer 2

[Cris LaPierre]

Another way that seems to also give the results you would get with vlookup is with discretize.

% Recreate your tables
holeid = [12;12;12;12;12;12;12;12;12;12;12;12;12;12;12;12];
depth = [0;0.0542;0.0801;0.2222;0.3959;0.4572;0.5110;0.5348;0.5712;0.6099;0.6437;0.6799;0.8011;0.8928;0.9590;1.0110];
rop = [118.7000;207.8000;139.6000;176.0000;177.8000;229.3000;242.4000;138.9000;85.7000;140.8000;164.5000;125.4000;189.8000;164.0000;118.4000;211.4000];
rophole12 = table(holeid,depth,rop);

holeid1 = [12;12;12;12;12;12;12;12;12;12;12;12;12];
depth1 = [0;0.13;0.22;0.36;0.48;0.56;0.67;0.78;0.84;0.96;1.03;1.14;1.24;];
gamma1 = [1;12;34;23;42;12;43;67;111;200;153;188;55];
gammahole12 = table(holeid1,depth1,gamma1);

% Use discretize to find the corresponding 'bin' gamma value
rophole12.gamma = discretize(rophole12.depth,[gammahole12.depth1;inf],gammahole12.gamma1)

rophole12 = 16×4 table
    holeid    depth      rop     gamma
    ______    ______    _____    _____

      12           0    118.7       1 
      12      0.0542    207.8       1 
      12      0.0801    139.6       1 
      12      0.2222      176      34 
      12      0.3959    177.8      23 
      12      0.4572    229.3      23 
      12       0.511    242.4      42 
      12      0.5348    138.9      42 
      12      0.5712     85.7      12 
      12      0.6099    140.8      12 
      12      0.6437    164.5      12 
      12      0.6799    125.4      43 
      12      0.8011    189.8      67 
      12      0.8928      164     111 
      12       0.959    118.4     111 
      12       1.011    211.4     200 

[Daniel Goldstein]

Thank you for multiple solutions. They are fantastic!