Equivalent to a Vloookup from Excel - can it be done?

Obviously it can be done, MatLab can do almost anything, I just can't, hence why I'm asking.

I have two separate arrays: Array A and Array B, each containing 500,000 values in the one column. Array A ranges from 0:265, while Array B ranges from 0:5000, both in a random order with values repeating etc etc.

I'm wanting eventually to plot a nice graph that, along the x-axis, goes 0:1:265 (i.e. Array A), and along the y-axis contains the average value of Array B, 0:1:5000.

In order to do this I'm trying to get a loop that runs through both the arrays and does something like:

• Finds all the values of ArrayA that are between 0:1.
• Take the corresponding values from ArrayB at the same points.
• Average the values taken from ArrayB.
• Then move onto finding the values in ArrayA that are between 1:2.
• Repeating all the way up to ArrayA between 264:265.
• Then plot the average values of ArrayB for each of the values of ArrayA.

I could do it in Excel with a VLookup function or an index match function, but the data isn't in Excel. I could make MatLab write the data to Excel, but that A. isn't the point, and B. would take Excel forever to go through.

Any help would be appreciated greatly!

Answer 1

[John D'Errico]

1. Take the floor of your array A. That will convert the elements to integers, 0 - 265.

2. Add one, since these will be used as indexes, and MATLAB uses 1-based indexing.

3. Read the help for accumarray. Use it, specifying @mean as the function to be used for accumulation.

Alternatively, you could have used ceil. It all depends on whether any elements that were EXACTLY an integer already (like 2) belong in the bin from 1 to 2, or the bin from 2 to 3. So the choice of floor or ceil will impact where things get sent.

[Ethan Murray]

I do apologise but I wasn't able to get it working from what you've suggested.

I've come to the following code:

Average = [];
index = [];
y = [];
HW_SP = round(HW_SP);
SOLAR = 0;

for z = 1:1:26501;
    index     = find(HW_SP == SOLAR);
    y       = HW_WP(index);
    Average(z,:) = mean(y);
    SOLAR        = SOLAR + 0.01;
end

Solar = (0:0.01:26502);
plot(Solar,Average);

Where: HW_WP and HW_SP are my two sets of resource data each an array with 527040 random values. HW_WP from 0:5000 and HW_SP from 0:265.

I could get the first iteration of the loop to work, however I couldn't get the 'Average' array to hold all the values. can you help with that?