Matlab Vlookup/Indexing equivalent

11 views (last 30 days)
Alex Wiest on 14 Sep 2022
Edited: Stephen23 on 14 Sep 2022
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!
Stephen23 on 14 Sep 2022
Edited: Stephen23 on 14 Sep 2022
"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:
Indexing is one of the important/fundamental topics that are introduced here:
But for your situation, you should probably use tables and one of the JOIN operators:

Stephen23 on 14 Sep 2022
Edited: Stephen23 on 14 Sep 2022
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

Categories

Find more on Matrices and Arrays in Help Center and File Exchange

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by