Multiple Criteria Look-Up Function within MATLAB

I would like to recreate the following VLOOKUP function within Matlab for multiple criteria:

For those unfamiliar with the EXCEL function, you essentially have to create a meta data column concatenating columns 1 & 2, that value is then used to lookup the corresponding value in the DIM 3 column.

Thanks in advance.

[johnsmithofmatlab]

I would like to comment that though there are questions which have been answered relating to VLOOKUP within Matlab. I couldn't find any which looked at multiple criteria with the result being another column.

Answer 1

[dpb]

If the data are defined as:

d1=repmat([2:5],5,1); d1=d1(:);
d2=repmat(1:5,1,4);d2=d2(:);
d3=100:100:2000;d3=d3(:);

then the lookup function can be defined as

fnVLU=@(i1,i2) d3(all([i1 i2]==[d1 d2],2));

Example:

>> fnVLU(3,4)
ans =
 900
>>

Above includes the specific table in the function; if they change routinely then the table can also be arguments.

ADDENDUM

An alternate if the lookup values are always constructed as shown...

N1=max(d2);       % size of subtable
fnIX=@(i1,i2) N1*(i1-d1(1))+d2(i2);

>> fnIX(3,4)
ans =
   9
>> d3(ans)
ans =
 900
>>

ADDENDUM

The actual problem is very far removed from the illustration so I'll leave it for pedagogical purposes.

For the last set of code that has issues outlined in comments below, a functional implementation awaiting resolution of those issues would look like (from the point onward I made a change)--

    ...
    % Use NaN to show missing locations more clearly...  
    ...    
    Table = [1:10;1:10;100:100:1000];
    % Define lookup function for the table -- NB: it's now horizontal
    fnL=@(SI,K) Table(3,all(Table(1:2,:)==[SI; K]));

    for i=1:length(D_Outer)
      for j=1:length(Pitch)
        for k=1:length(Free_Length)
          for q=1:length(D_Wire)
            if strcmp(Spring_Type,'Open');
              Pitch(k) = (Free_Length(k) - D_Wire(q))/A_Coils;
            elseif strcmp(Spring_Type,'Open and Ground');
              Pitch(k) = Free_Length(k)/Tot_Coils;
            elseif strcmp(Spring_Type,'Closed');
              Pitch(k) = (Free_Length(k) - (3*D_Wire(q)))/A_Coils;
            elseif strcmp(Spring_Type,'Closed and Ground');
              Pitch(k) = (Free_Length(k) - (2*D_Wire(q)))/A_Coils;
            end
            Factor_K(k) = Pitch(k)/D_Wire(q);    
            Spring_Index(i) = D_Outer(i)/D_Wire(q);  
            % with above table there are many points for which 
            % there won't be a match and fnL() will return an
            % empty result.  Enclose in try...catch block to not crash
            % Preallocated Factor_L to NaN so they'll show up clearly
            try
              Factor_L(i)=fnL(Factor_K(k),Spring_Index(i));
            catch
              Factor_L(i)=0;  % so will show which didn't match
            end
          end
        end
      end
    end

Owing to the lack of points intersecting in the table and the issue with overwriting the indices in the result vectors, this yields less than satisfying result of

>> [[Spring_Index nan(1,5)];Factor_K;[Factor_L nan(1,5)]]
ans =
    2.0000    4.0000    6.0000    8.0000   10.0000   12.0000   14.0000   16.0000   18.0000   20.0000       NaN       NaN       NaN       NaN       NaN
         0    0.5000    1.0000    1.5000    2.0000    2.5000    3.0000    3.5000    4.0000    4.5000    5.0000    5.5000    6.0000    6.5000    7.0000
         0         0         0         0         0         0         0         0         0         0       NaN       NaN       NaN       NaN       NaN
>>

in that no results were saved that had intersecting values in Table

I added another diagnostic display just to illustrate the code itself functions properly and that it's the data and indexing issues that are the problem where output

disp([i k Factor_K(k),Spring_Index(i) Factor_L(i)])

in the try block so could see the successes overall in the loop besides just the failures that finally were saved. That resulted in

>> jsmith
     i     k    F_K   SI   F_L  % added header for column ID -- dpb
     1     5     2     2   200
     1     5     2     2   200
     1     5     2     2   200
     1     5     2     2   200
     1     5     2     2   200
     1     5     2     2   200
     1     5     2     2   200
     1     5     2     2   200
     1     5     2     2   200
     1     5     2     2   200
     1     5     2     2   200
     1     5     2     2   200
     1     5     2     2   200
     1     5     2     2   200
     1     5     2     2   200
     2     9     4     4   400
     2     9     4     4   400
     2     9     4     4   400
     2     9     4     4   400
     2     9     4     4   400
     2     9     4     4   400
     2     9     4     4   400
     2     9     4     4   400
     2     9     4     4   400
     2     9     4     4   400
     2     9     4     4   400
     2     9     4     4   400
     2     9     4     4   400
     2     9     4     4   400
     2     9     4     4   400
     3    13     6     6   600
     3    13     6     6   600
     3    13     6     6   600
     3    13     6     6   600
     3    13     6     6   600
     3    13     6     6   600
     3    13     6     6   600
     3    13     6     6   600
     3    13     6     6   600
     3    13     6     6   600
     3    13     6     6   600
     3    13     6     6   600
     3    13     6     6   600
     3    13     6     6   600
     3    13     6     6   600
>>

This illustrates clearly the over-storing into i, k indices that are repeated and that only the locations in the Table with same values match as presently constructed.

But, the lookup works as supposed to; it's just that there's no data available for almost all the cases.

[johnsmithofmatlab]

Hi, Thanks for the response, the answer you presented works for the example I showed above. But perhaps I should have been more specific. I am using DIM 1 & DIM 2 within a FOR loop with an pre-defined table. I am interested in calculating DIM1 & DIM2 within the for loop and within each iteration look at the table and determine the corresponding DIM3 value.

DIM1 = zeros(1,length(V1));
V2 = zeros(1,length(V3));
DIM3 = zeros(1,length(DIM1));
DIM2 = zeros(1,length(V2));

V1 = [1:1:10];
V2 = [1:1:10];
V3 = [1:1:15];
V4 = [0.1:0.1:0.5];
DIM3=[100:100:2000];

for i=[1:1:length(V1)];
  for j=[1:1:length(V2)];
    for k=[1:1:length(V3)];
      for q=[1:1:length(V4)];
        V2 = V3 - (2*V4)
        DIM1(i) = [V1(i)/V4(q)];  
        DIM2(k) = V2(k)/D4(q);     
      end
    end
  end
end

[dpb]

So, no problem...but where are the variables for the predefined table?

Build the anonymous function using them as illustrated for my choice of variables and then call it with the computed DIMn values.

NB: The two arrays above are going to be overwritten J*K*Q times for DIM1 and I*J*Q times for DIM2 where the uppercase variables indicate the upper limit for the respective FOR loops. It wouldn't appear there's any point in indexing them at all.

Also, I didn't check but need to ensure that the ratios are in fact integers and inside the range of the lookup table dimensions. D4 isn't defined in the above snippet...

[johnsmithofmatlab]

Spring_Type = 'Closed and Ground';
A_Coils = 4; % n
D_Coils = 2; % n
T_Coils = A_Coils + D_Coils;
D_Outer = [1:1:10];
Pitch = [1:1:10];
Free_Length = [1:1:10];
D_Wire = [0.1:0.1:1];

Spring_Index = zeros(1,length(D_Outer));
Pitch = zeros(1,length(Free_Length));
Factor_L = zeros(1,length(Spring_Index));
Factor_K = zeros(1,length(Pitch));

Table = [1:10;1:10;100:100:1000];

for i=[1:1:length(D_Outer)];
  for j=[1:1:length(Pitch)];
    for k=[1:1:length(Free_Length)];
      for q=[1:1:length(D_Wire)];
        if strcmp(Spring_Type,'Open');
          Pitch(k) = (Free_Length(k) - D_Wire(q))/A_Coils;
        elseif strcmp(Spring_Type,'Open and Ground');
          Pitch(k) = Free_Length(k)/Tot_Coils;
        elseif strcmp(Spring_Type,'Closed');
          Pitch(k) = (Free_Length(k) - (3*D_Wire(q)))/A_Coils;
        elseif strcmp(Spring_Type,'Closed and Ground');
          Pitch(k) = (Free_Length(k) - (2*D_Wire(q)))/A_Coils;
        end
        Factor_K(k) = Pitch(k)/D_Wire(q);    
        Spring_Index(i) = [D_Outer(i)/D_Wire(q)];   
      end
    end
  end
end

    % Table has 3 rows. Row 1: Spring Index, Row 2: Factor K, Row 3: Factor L
    % For a given Spring Index, and Factor K, I would like to return a Factor L
    % value for each iteration of the for loop.

I've enclosed the code I was looking at, I changed the variables to make it easier to follow, perhaps I oversimplified it. I've put an explanation in the comments in the FOR loop. I hope that helps.

[dpb]

 Table = [1:10;1:10;100:100:1000];

Has the problem there are only entries for SI==K but same logic will work presuming again it's been oversimplified removing the cross terms.

It also has problem that Factor_K isn't integer-valued and there are no entries in the table to match. This doesn't really look much at all like the spreadsheet example???

Is there an index or are there actual computed values in the real Table; which makes a big difference in how to code it robustly (exact equality testing is risky for floating point unless can guarantee same rounding which can be difficult in practice)???

[dpb]

There's still the issue of overwriting the variables inside the Q loop; what is returned for the Spring_Index in the end is only that which appropriate for the last pass through the loop...that might be ok depending on what else is being done with those arrays later on or maybe they're not even used??? In that case, as noted before, there doesn't seem to be any reason to actually dimension them and save them. Here's a quick demo of what's computed vis a vis what's saved; there's a similar issue with Factor_K and Pitch but the pattern is different for subscript k instead of i.

As noted, this MAY be ok but need be aware and careful in how (if do) use those arrays later on in the code.

>> for i=1:length(D_Outer),disp(D_Outer(i)./D_Wire),end
   10.0000    5.0000    3.3333    2.5000    2.0000
   20.0000   10.0000    6.6667    5.0000    4.0000
   30.0000   15.0000   10.0000    7.5000    6.0000
   40.0000   20.0000   13.3333   10.0000    8.0000
   50.0000   25.0000   16.6667   12.5000   10.0000
   60.0000   30.0000   20.0000   15.0000   12.0000
   70.0000   35.0000   23.3333   17.5000   14.0000
   80.0000   40.0000   26.6667   20.0000   16.0000
   90.0000   45.0000   30.0000   22.5000   18.0000
  100.0000   50.0000   33.3333   25.0000   20.0000
>> Spring_Index
Spring_Index =
     2     4     6     8    10    12    14    16    18    20
>>

NB: The saved vector is last column/pass of the multiple loops.