Looking up values using multiple criteria, resulting in a funky result

[8Jinx]

Short and sweet description:

Spreadsheet 'Raw Table' has many, many rows, and a dozen columns of raw input data. Each row has date, number, and text fields.

Spreadsheet 'Dashboard' is a somewhat simple one page summary of one day's worth of data entry from Raw Table. Dashboard isn't a simple table, but more form-like in appearance (not sure if that matters). Essentially, the values from Raw Table are spread out in different locations in Dashboard, and not just in a row.

The Dashboard fields gets their values from Raw Table based multiple criteria, the most important being a manually inputted date entry at the top of the Dashboard. The other criteria are simple numbers (ie, 1, 2, 3, 4....) built into the formula. So a set of criteria for one particular cell in the dashboard could be: 4/6/2014, 1, 3. Those 3 criteria are unique identifiers: there is only one match, ever. If those criteria are met, then a value from a specific spot in that row from Raw Data is placed in the dashboard field.

Here is one formula example for one such field in Dashboard:

=INDEX(('RAW DATA'!$D$1:$D$20000),SUMPRODUCT(('RAW DATA'!$A$1:$A$20000=$D$1)*('RAW DATA'!$B$1:$B$20000=1)*('RAW DATA'!$C$1:$C$20000=3)*ROW('RAW DATA'!$D$1:$D$20000)))

The first sumproduct criteria is a match for the date in Raw Data's column A with the manually inputted date in Dashboard located at D1.

The second criteria is match for the value in column B with a value of 1. The third criteria is match for the value in column C with a value of 3. (Again, this is an example. The 1,3 set can be any specific combo from 1,1 to 8,8. Thus, a day's data can have no more than 64 records of a dozen date, number and text fields.)

The Index function is obviously array,row,column. So this example index function results in the contents of column D in the row that the sumproduct function generates.

This dashboard spreadsheet works perfectly everywhere except four fields (out of 100+ instances of the same formula), and its driving me nuts. In the faulty spots, the value that the index function is returning in Dashboard is basically the contents of column D in Raw Table in the row that corresponds to the place the formula is located in Dashboard. It's almost like there's an error in the formula result, and the index is defaulting to the row number in Dashboard. But I've tested this 8 ways til Sunday. I can even straight-up copy these four faulty fields to another part of the dashboard, and the results are magically correct. And I've checked to see that the actual sumproduct function result is returning the correct row for these fields. I don't have an answer as to why it's defaulting to the formula row number in just these four locations, and not the row value that the sumproduct function is correctly producing.

Any ideas? I've thought maybe it was a data type problem in Raw Data, but that route produced a dead end. It has to be something with the Dashboard. I'm going to start over with a new Dashboard spreadsheet, and see what happens. One thing I did was, early in development of the dashboard, use ctrl-shift-enter in the first faulty field, then went back and changed the formula wholesale to something different, and copied that cell to (at least I think) the three other faulty spots. But if I went back and completely changed the formulas and did away with the brackets, the cells should be fresh, no?

Any insight would be much appreciated.

[Duoae]

Hi 8Jinx,

Maybe I'm missing something here but the formula you posted shouldn't work if I'm reading it correctly. It essentially says: =Index(Array,row) where the row=sumproduct(array1). Normally you'd have commas between the variables, no? You also make no reference to the fourth part of array1 in the sumproduct. If it's correct then I don't understand why you would need the sumproduct when you've already performed the calculation anyway...

I think an example spreadsheet would be helpful to understand this issue as it's pretty hard to visualise.

[Kaper]

If it is in corresponding row, my first idea would be to check if you comitted it as array formula (Ctrl+Shift+Enter). Probably it would be easier to judge if a sample set of data with such strange results is provided - see http://www.excelforum.com/forum-rule...rum-rules.html how to post an attachment)

@Duoane - no. this form shall work too. in sumproduct you can have one array as well as many, especially in this case. By using multiplying (not a list of) values one does not have to bother of bollean results. So it is equivalent to:
=INDEX(('RAW DATA'!$D$1:$D$20000),SUMPRODUCT(1*('RAW DATA'!$A$1:$A$20000=$D$1),1*('RAW DATA'!$B$1:$B$20000=1),1*('RAW DATA'!$C$1:$C$20000=3),ROW('RAW DATA'!$D$1:$D$20000)))

[Duoae]

Thanks for explaining that, Kaper. Never come across that before!

[Kaper]

You are welcome :-)
Let's see what is Thread Author comment.

[8Jinx]

What happens when you use my formula and it returns a zero for a row number? What's the result for Index(array,0,0)?

In other words, if my formula searches the raw data table and finds no matches, then the way it's written it would be =INDEX(('RAW DATA'!$D$1:$D$20000),0,0)), correct? Could it be generating an error with a zero column, zero row? Does it default to the corresponding row of the formula? It seems to work perfect if there is a match. But it produces the funky result when it does not uncover a match. Is there a way around this, like writing a long IF statement, like:

=IF(SUMPRODUCT(('RAW DATA'!$A$1:$A$20000=$D$1)*('RAW DATA'!$B$1:$B$20000=1)*('RAW DATA'!$C$1:$C$20000=3)*ROW('RAW DATA'!$D$1:$D$20000))=0, "0", INDEX(('RAW DATA'!$D$1:$D$20000),SUMPRODUCT(('RAW DATA'!$A$1:$A$20000=$D$1)*('RAW DATA'!$B$1:$B$20000=1)*('RAW DATA'!$C$1:$C$20000=3)*ROW('RAW DATA'!$D$1:$D$20000))))

I almost went blind writing that. Imagine my laptop having to run that in a hundred cells.

[8Jinx]

My suggested fix works, by the way. It produces the correct results, and spits out a "0" if there is no match for the criteria. I can live with this.