Multiple column lookup

[TrishB]

I have source data (example in A3:G9 in the attached) which is a dump from a database and I cannot change how it outputs, it is outputed each month with the old month dropping off and a new month added each month (the dates and data rows are not static). I need to be able to lookup/reference this data to fill in another spreadsheet, but it is also in a pre-defined format (example in A15:D25) with other calculations among the database data. The database data sorts by Date as part of the output. The example is a very small snapshot - the real data is 3 years of monthly values for around 300 IDs, across 8 Value/ID columns.

I'm after a formula which I will be able to use regardless of if the ID is in column C, E or G and return the value in the corresponding Value column (which is the column BEFORE the ID) for the given date. I can make an INDEX and MATCH combination work for just one ID/Value set (i.e. M94 using date and columns B&C), but cannot make it work to look in columns E&D if the ID is not in column C (e.g. for M96) or G&F if not in C or E (e.g. M97). The cells in yellow are where I need results. My current INDEX/MATCH formula is in C17,19 & 20 - while I could use this (IDs in column C will always be in column C each time the database data is pulled), it will be tedious to match which formula goes in which row in the 2nd spreadsheet.

Thanks,
Trish

[Glenn Kennedy]

Ummm. Where did the results for M95 and M99 come from???? If that's a mistake on your part, use this:

=IFERROR(1/(1/SUMPRODUCT(--($C$4:$G$9=$A17),($B$4:$F$9),($A$4:$A$9=C$15)+N(T(LEN($C$3:$G$3))))),"")

[Bo_Ry]

Please try at C17 and copy over

=SUMPRODUCT($B$4:$F$9,($A$4:$A$9=C$15)*($C$4:$G$9=$A17))

Or add more if to get all result

=IF(LEFT($A17)="s","",IF((LEFT($A17)="M")*($B17=""),C16*C18,SUMPRODUCT($B$4:$F$9,($A$4:$A$9=C$15)*($C$4:$G$9=$A17))))

[Glenn Kennedy]

Bo_Ry. A question. Your first formula I can see that it works... and I made up another sample sheet and it worked there, too. However, I don't understand HOW it works. Why does the use of , instead of * avoid the need for redimensioning the array? I haven't seen this before.

[Bo_Ry]

Glenn,
=SUMPRODUCT($B$4:$F$9,($A$4:$A$9=D$15)*($C$4:$G$9=$A17))

I think I learn from you that we can use , to avoid error.

($A$4:$A$9=D$15)*($C$4:$G$9=$A17)

$C$4:$G$9 is 6 rows x 5 columns
$A$4:$A$9 is 6 rows x 1 columns
(6 rows x 5 columns)*(6 rows x 1 columns) = 6 rows x 5 columns

This is all multiple Boolean, we will get 0,1 no error

$B$4:$F$9 this content number and text if just do $B$4:$F$9*($A$4:$A$9=D$15)*($C$4:$G$9=$A17) we will get error

From ($A$4:$A$9=D$15)*($C$4:$G$9=$A17) 1 will show up at same position of Number in $B$4:$F$9, text will go with 0
This is my guess why Sumproduct work with "," but I don't know for sure.

[Glenn Kennedy]

I think I learn from you that we can use , to avoid error.

And I picked it up from Teylyn's www site... Small world!!

[TrishB]

Thanks Glenn,

The formula for M95 and M99 is a basic version of what is in the spreadsheet which uses the database data. We're ok with those formulas, its how to (easily) get the database data from its output dump into the 2nd visually understandable spreadsheet which we need help with.

[TrishB]

Please try at C17 and copy over

=SUMPRODUCT($B$4:$F$9,($A$4:$A$9=C$15)*($C$4:$G$9=$A17))

Or add more if to get all result

=IF(LEFT($A17)="s","",IF((LEFT($A17)="M")*($B17=""),C16*C18,SUMPRODUCT($B$4:$F$9,($A$4:$A$9=C$15)*($C$4:$G$9=$A17))))

Thank you Bo, that seems to work Will try it in the actual data in the morning.