Recreating Vlookup from excel
1 view (last 30 days)
Show older comments
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 Comment
Guillaume
on 24 Sep 2014
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.
Accepted Answer
A Jenkins
on 9 Jun 2014
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 Comments
Guillaume
on 24 Sep 2014
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')
More Answers (1)
Max
on 2 May 2015
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 Comments
selina dong
on 8 Dec 2015
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
on 8 Dec 2015
The answer is very simple, but please start your own question rather than hijacking somebody's else.
See Also
Categories
Find more on Database Toolbox 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!