Recreating Vlookup from excel
3 次查看(过去 30 天)
显示 更早的评论
I am trying to recreate vlookup (the excel function) in matlab. Having trawled through numerous other questions, no solutions I have found do what I am looking for. A brief example is here. I have a set of objects each with a unique ID number. Database A contains a selection of IDs. Database B contains all of the IDs, plus additional attributes. I want to append the additional information per row from database B onto the end of database A.
So for example, if: A = [ 1; 2; 6; 7]
and B = [1 56.7; 2 45.1; 3 65.7; 4 76.1; 5 34.1; 6 34.1; 7 121.1]
I would like to change A to be A = [1 56.7; 2 45.1; 6 34.1; 7 121.1]
However, in my data I need to print the results from A in the 82nd column of B, and they need to be derived from the 8th column of A.
The following does not work for my purpose, because 'e' needs to change to be printed per row: http://www.mathworks.co.uk/matlabcentral/fileexchange/29233-vlookup-similar-to-ms-excel-function/content/vlookup.m
I also tried interp1 function in the following way based on previous results:
c=interp1(B(:,1),B(:,2),A(:,1))
But I need exact matches to my data, not interpolated values.
Any help much appreciated.
1 个评论
Guillaume
2014-9-24
See also How to find a subset matrix in the main matrix and build a new matrix which is very similar.
ismember is the function you need.
采纳的回答
A Jenkins
2014-6-9
Purely numeric and all index of A are guaranteed to be in B?
A = [ 1; 2; 6; 7];
B = [1 56.7; 2 45.1; 3 65.7; 4 76.1; 5 34.1; 6 34.1; 7 121.1];
A_col_id=1; % the column the ids are in matrix A
B_col_id=1; % the column the ids are in matrix B
A_col_value=2; % the column the values should go in matrix A
B_col_value=2; % the column the values are in matrix B
% real code
[~,locb]=ismember(A(:,A_col_id),B(:,B_col_id));
A(:,A_col_value)=B(locb,B_col_value)
3 个评论
Guillaume
2014-9-24
Dave, if you mean to say that the key is made of three columns, then you would use
ismember(A(:, A_col_keys), B(:, B_col_keys), 'rows')
更多回答(1 个)
Max
2015-5-2
For my own problem i found an answer without the usage of ismember but with a for loop also suitable for this problem:
A = [ 1; 2; 6; 7];
B = [1 56.7; 2 45.1; 3 65.7; 4 76.1; 5 34.1; 6 34.1; 7 121.1];
for i=1:length(A)
A(i,2)=B(B(:,1)==A(i,1),2);
end
2 个评论
selina dong
2015-12-8
any idea how to include the criteria that when there is no corresponding match in B, fill with zero or NaN? At the moment it would give an error and would not run: 'Subscripted assignment dimension mismatch.'
thanks!
Guillaume
2015-12-8
The answer is very simple, but please start your own question rather than hijacking somebody's else.
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Matrix Indexing 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!