Recreating Vlookup from excel

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.

[Guillaume]

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.

Answer 1

[A Jenkins]

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)

[Dave]

Following your answer, what if there are not 1 but 3 col_ids in A and B? The col_value are still in one column.

[Guillaume]

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]

Merci beaucoup. I'll check with the whole set but looks like suits perfectly.

Answer 2

[Max]

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

[selina dong]

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]

The answer is very simple, but please start your own question rather than hijacking somebody's else.