Recreating Vlookup from excel

2 次查看（过去 30 天）
Jeremy
Jeremy 2014-6-9
评论： Guillaume 2015-12-8
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.

请先登录，再回答此问题。

采纳的回答

A Jenkins
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 个评论
显示 1更早的评论
Guillaume
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')
Dave
Dave 2014-9-24
Merci beaucoup. I'll check with the whole set but looks like suits perfectly.

请先登录，再进行评论。

更多回答（1 个）

Max
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
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
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 中查找有关 Database Toolbox 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by