VLookup Multiple Conditions if statements

Hi

I was wanting to modify this code to perform a VLookup on 2 columns and return the element of one of the correspoding column element in the same row?

Can someone please assist me on the if statements for this function?

Here is a function I found on Mathworks that I would like to modify: So instead of having one lookup column I need 2.

https://www.mathworks.com/matlabcentral/fileexchange/29233-vlookup-similar-to-ms-excel-function

[Adam Danz]

James, rather than sharing the code directly, provide a link to the function and then remove the copyrighted code from your question.

[James]

ok, I provided the link. Thanks

[Adam Danz]

Thanks. If your searching a numeric matrix, my answer should do the trick. If you're searching a cell array, describe the content of the array (strings, characters, numbers, vectors, etc...) and I could help with a solution.

[James]

Thanks. I'm trying to search both a numeric matrix and string/characters.

The first search column is numeric. The second search column is a string/character. The third column is numeric float:

Example:

Col1 Col2 Col3

1 Hello 4.57

2 Day 3.50

3 Day 1.20

So say I want to look for the value 2 and the word Day and return the value in the adjacent col3

If col1 is 2 and the adjacent col2 is Day then return the value in the adjacent col3, in this case 3.50.

Thanks for your help

[Adam Danz]

Is the array a cell array or a table?

[James]

Hi Adam,

The array could be either a cell array or table. Is this a problem for coding?

Thanks so much.

[Adam Danz]

See updated answer.

Answer 1

[Adam Danz]

No need for a function.

See inline comments for details.

Matrices

% Define matrix 
m = randi(3,2000,5); 

% Define search variables
lookupValues = [1,3];       % values to search for in the two columns 
lookupColumns  = [2,3];     % two column numbers to search in 
returnColumn = 1;           % the column number used to return the output

% Compute outputs (same variable names as the function you shared)
index = ismember(m(:,lookupColumns), lookupValues, 'rows');
content = m(index, returnColumn); 

Cell arrays

Look up the value of 2 in column 1 and 'Day' in column 2:

idx = [C{:,1}]'==2 & strcmpi(C(:,2), 'Day'); 
C(idx,3)  % or [C{idx, 3}]

Tables

Look up the value of 2 in column 1 and 'Day' in column 2:

idx = [T{:,1}]==2 & strcmpi([T{:,2}], 'Day');
T(idx,3)  % or [T{idx, 3}]

[James]

I keep getting the following error when using the cell array answer:

Error using &

Inputs must have the same size.

[Adam Danz]

My bet is that you missed the transpose. Could you copy that part of the code you're using?

[C{:,1}]'
%       ^transpose

[James]

I have the transpose. I think it could be the xls spreadsheet that I'm reading into matlab. Can I send this file to you offline?

I'm reading in the file like this:

[num,txt,raw] = xlsread('Stuff');

The data is read into a cell array with headers for the raw.

I am using your code to do the lookup on the 'raw' cell array.

When I just make a simple cell array in matlab like this:

C = {1, 'Day', 4.57;

1, 'Day', 3.50;

2, 'Hello', 1.20};

Your code works fine. But it doesn't work on the data file I'm reading in.

I guess what I want to do in read in the spreadsheet without the headers into a cell array or table and then do the lookup using your code.

[Adam Danz]

You can attach the data to one of these comments if you'd like but before doing so, try reading the data into a table using T = readtable(filename.xlsx) (be sure to include the file extension).

Then use the table method of vlookup.

If you get the error message again, please include the full copy-pasted message (all of it) and show the first few rows of data using head(T) for tables or C(1:10, :) for cell array.

[James]

ok thanks will do.

[James]

Thank you so much! That worked perfectly!. Sorry for all the questions.

[Adam Danz]

Glad I could help!

[James]

Hi Adam,

Could you help me with plotting?

[Adam Danz]

What are you plotting? If it's a quick question with a straightforward answer, I can help you here. If the question is more in depth, you may want to write a new question and leave a comment with the URL.