×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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

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

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.

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

(OP)
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
***************

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources