Matlab Vlookup/Indexing equivalent

I have a datetime array "t" that is 78888x1 in dimension like this, that is every hour for a period of time

'01-Jan-2027 00:00:00'

'01-Jan-2027 01:00:00'

'01-Jan-2027 02:00:00'

'01-Jan-2027 03:00:00'

'01-Jan-2027 04:00:00'

I also have a 9x4 matrix "input" that has yearly data, like this

2027 1.2 200 350

2028 3.7 200 350

2029 7.2 200 350

2030 7.8 200 350

2031 9.3 200 350

2032 9.3 200 350

2033 9.3 200 350

2034 10.9 200 350

2035 15.5 200 350

What i am trying to do is for every row in array "t", look up the year and index it with the year in column 1 from matrix "input" and then take the corresponding value from column 2,3 and 4 and copy into a new array called "output".

I am a matlab newbie, and am at a loss on where to start/how to go about this.

Any help is appreciated!

[Rik]

You describe several steps. Which ones have you tried? Where are you stuck? What code have you tried?

[Alex Wiest]

I have input the date as array/matrix into Matlab. I have then created an array of just the years of array “t” using t_year=year(t)

The next step logically would be to index/lookup between the two datasets. I spent yesterday evening searching the forums through the various “lookup” questions others have posted but nothing was similar in fashion to what I am trying to do.

As mentioned I am a newbie here and between a matlab guidebook and this MathWorks site I am trying to skill up but this particular step I am completely at a loss on how to proceed as there is no “lookup” function in matlab

[Stephen23]

"I am completely at a loss on how to proceed as there is no “lookup” function in matlab"

The general solution is called indexing.

Indexing is simpler, yet more powerful and much more versatile than any Excel function:

https://www.mathworks.com/company/newsletters/articles/matrix-indexing-in-matlab.html

Indexing is one of the important/fundamental topics that are introduced here:

https://www.mathworks.com/help/matlab/getting-started-with-matlab.html

But for your situation, you should probably use tables and one of the JOIN operators:

https://www.mathworks.com/help/matlab/ref/table.join.html

Answer 1

[Stephen23]

DT = [datetime(2027,1,1,(0:4).',0,0);datetime(2028,1,1,0,0,0)]

DT = 6×1 datetime array
   01-Jan-2027 00:00:00
   01-Jan-2027 01:00:00
   01-Jan-2027 02:00:00
   01-Jan-2027 03:00:00
   01-Jan-2027 04:00:00
   01-Jan-2028 00:00:00

Y = year(DT);
M = [2027,1.2,200,350;2028,3.7,200,350;2029,7.2,200,350;2030,7.8,200,350;2031,9.3,200,350;2032,9.3,200,350;2033,9.3,200,350;2034,10.9,200,350;2035,15.5,200,350]

M = 9×4

1.0e+03 * 2.0270 0.0012 0.2000 0.3500 2.0280 0.0037 0.2000 0.3500 2.0290 0.0072 0.2000 0.3500 2.0300 0.0078 0.2000 0.3500 2.0310 0.0093 0.2000 0.3500 2.0320 0.0093 0.2000 0.3500 2.0330 0.0093 0.2000 0.3500 2.0340 0.0109 0.2000 0.3500 2.0350 0.0155 0.2000 0.3500

Approach one: ISMEMBER and indexing:

[idx,idy] = ismember(Y,M(:,1));
out = M(idy(idx),2:end)

out = 6×3

1.2000 200.0000 350.0000 1.2000 200.0000 350.0000 1.2000 200.0000 350.0000 1.2000 200.0000 350.0000 1.2000 200.0000 350.0000 3.7000 200.0000 350.0000

Approach two: tables and JOIN:

T1 = table(DT,Y)

T1 = 6×2 table
             DT              Y  
    ____________________    ____

    01-Jan-2027 00:00:00    2027
    01-Jan-2027 01:00:00    2027
    01-Jan-2027 02:00:00    2027
    01-Jan-2027 03:00:00    2027
    01-Jan-2027 04:00:00    2027
    01-Jan-2028 00:00:00    2028

T2 = array2table(M, 'VariableNames',{'Y','a','b','c'})

T2 = 9×4 table
     Y       a       b      c 
    ____    ____    ___    ___

    2027     1.2    200    350
    2028     3.7    200    350
    2029     7.2    200    350
    2030     7.8    200    350
    2031     9.3    200    350
    2032     9.3    200    350
    2033     9.3    200    350
    2034    10.9    200    350
    2035    15.5    200    350

T3 = join(T1,T2)

T3 = 6×5 table
             DT              Y       a      b      c 
    ____________________    ____    ___    ___    ___

    01-Jan-2027 00:00:00    2027    1.2    200    350
    01-Jan-2027 01:00:00    2027    1.2    200    350
    01-Jan-2027 02:00:00    2027    1.2    200    350
    01-Jan-2027 03:00:00    2027    1.2    200    350
    01-Jan-2027 04:00:00    2027    1.2    200    350
    01-Jan-2028 00:00:00    2028    3.7    200    350