Thread: allocate values to specific rows

Hi all,

I have 9 samples in which a number of species were found, but the number and kind of species vary between samples. Now I'd like to create a pivot table to have an overview of which species was found where and the amount of individuals in each sample. In order to create this pivot table, I need to gather information from different tables.
Through applying filters I have made a column with every species found throughout the samples (unique values). Now I want to allocate the number of individuals of a certain species found in every sample to the corresponding row.

I've used the lookup formula:
=LOOKUP(A2;L$2:L$92;M$2:M$92)

with A the column with the unique values
L the column of a sample
M the number of individuals

This works only partly.
Until it comes across a match, it gives N/A, which is ok, but when the first match is found, it gives the number of individuals of that specific match until the next match, and repeats this 'behaviour' for every match it encounters.

How can I avoid this extrapolation of values?

Thanks in advance!

post a workbook

use
=INDEX($M$1:$M$36;MATCH(A2;$L$1:$L$36;0))

try for max in e3 put
=MAX(($A$3:$A$79=D3)*($B$3:$C$79)) array entered with ctrl+shift+enter