How to lookup data values from table when using multiple cells?

[mistermopar]

This is probably not a big deal to the Excel gurus out there, but I've been looking on the net for hours this morning trying to figure this out and I'm still having no luck.

In the attached spreadsheet, I have a fairly large data table (SCS tabular hydrograph data), and that table doesn't have "unique" values, there are 36 values for each Tc, 12 values for each Ia/p value, and then also 12 time slots). I need to pull out the correct value by narrowing my search using all 3 input values to arrive at the correct data line.
See the red values in the attached spreadsheet for the input string, I'm trying to 'fill out' the white cells to the right using data pulled from the correct data line below.

To arrive at a unique answer, I need to have 3 values match to decide which LINE of the data table is correct. This is where I'm having the issue. Vlookup & Match, and other common functions do not seem to support multiple inputs (at least I can't figure out how to input an array).

I imagine this to be relatively complex 'nested' formula of sorts, but it seems I'm in over my head, I'm just ok with excel, not a master by any means.

Thanks for any help.
-sam

 

[zelgar]

I don't know if there's an easy way, but it would be fairly simple to make a macro to do what you want.

 

[mistermopar]

I was able to find an excel guru over here that just answered my question:

http://tinyurl.com/nv627u

I hadn't really gave SUMPRODUCT a 2nd glance for this case, but it was the way to go! I'll post it here in case it helps someone else with a similar problem.

***************
Try, in F4:

=SUMPRODUCT(--($C$46:$C$405=$C4),--($D$46:$D$405=$D4),--($E$46:$E$405=$E4),F$46:F$405)

copied down and across the table
***************

 

[IDS]

This problem is also discussed here:

http://www.dailydoseofexcel.com/archives/2009/04/21/vlookup-on-two-columns/

A couple of posters make the point that this is exactly what DGET (or DSUM) are intended for.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/