Hi,

I need help with writing a formula or a macro to compare the row name with the column header and displaying the appropriate column value.

For ex:

Capture.JPG

In the above example, for each item in Column A, there is a rightful owner in Column B. However Column C:F displays the count of each item possessed by different owners.

So Row 3 indicates that QW possess 2 units of Item 100 and RT possess 1 unit of Item 100. But rightful owner AB possess nothing.

In order to find the number of items possessed by the rightful owner, I need a macro or a simple formula..

Expected solution would look like this
Capture1.JPG

I can use the following formula
=IF(B3=$C$2,C3,IF(B3=$D$2,D3,IF(B3=$E$2,E3,IF(B3=$F$2,F3,"Not found"))))
but a simpler version will be much helpful..