INDEX, MATCH: multiple criteria with iterative data

**boai** · 01-04-2013, 03:45 PM

Hi there,
Since this is my first and hopefully not last post, I would like to introduce myselfe. I am 26, female, living in Zurich Switzerland and studying at the University of Zurich and working part time in the financial industry.
I have a sheet with different identification numbers in the top row and trade date with corresponding daily prices in the columns (sheet full). I now want to find (in sheet TOTAL) for a specific date and identification number the price. The problem is that the identification numbers have common but not totally equal trade dates.
I tried to solve this problem with a SUMIFS function and also with INDEX and two MATCH nested.
Any ideas?

**ChemistB** · 01-04-2013, 04:19 PM

What do you mean by "common but not totally equal trade dates" In your example, you have mostly dates from 2011 in full for two of your IDs. For CH0011074785, you have a lot of gaps. Do you want the last P-last prior to a blank date?

**boai** · 01-04-2013, 04:27 PM

Hii, thanks for your answer. I only want the fields to be filled with the original P-last, not a copy of the last p-last. The sheet I am working with contains over 300 IDs, most of them have 450-500 trading days. I just mentioned this because I want to emphasize that each ID is different, so the search has to be done by the correct ID and the specific date (horizontally and vertically).

**Haseeb Avarakkan** · 01-04-2013, 07:49 PM

Try this in TOTAL!B2,

=SUMIF(INDEX(Full!$A:$H,0,MATCH($A2,Full!$A$1:$H$1,0)),B$1,INDEX(Full!$A:$H,0,MATCH($A2,Full!$A$1:$H$1,0)+1))

Then copy down & across. Adjust the ranges.