Excel: Complex lookup

[wayneryeven]

Hey all
Lookup time...

Ok. Sheet 1 has 2 columns, Machine and Qty.

Machine-Qty
1-25
2-10
3-20

What we are saying here is Machine 1 can make 25 units.

On sheet2 i have another table that basically has Machine in column A, Output column B, People column C.

Machine-Output-People
1-50-3
1-100-4

What i am saying here is that Machine 1 is capable of making 200 units which requires 6 people.

Now what i want to do is add an additional column into Sheet 1 called People. I want to look up how many people are required to cover the Qty.

Hence, going back to Sheet 1:
Machine Qty
1 5

If i want to make 25 units on machine 1 then i require 3 people.

This is achiveed from looking at Sheet 2:
Machine-Output-People
1-50-3
1-100-4

Machine 1 is capable of making 50 units with 3 people. As 25 is less than 50 this satisfies the demand and hence 3 will be entered against this row in Sheet1.

IF the Quantity was 75 then it should require 4 people as 3 people achieve 50, 4 people achive maximum of 100.

Ok still with me??! I hope i explained this clearly, i appreciate its quite complex but if anyone has any suggestions on how best to resolve this ill gladly listen.

Cheers guys,
wayner1980

 

[kjv1611]

This may have been a typo, but the logic looks to be off, from what I can tell, in this section:

Machine-Output-People
1-50-3
1-100-4

What i am saying here is that Machine 1 is capable of making 200 units which requires 6 people.

Or maybe I'm just missing something?

 

[wayneryeven]

Oops sorry should have been:

What i am saying here is that Machine 1 is capable of making 50 units which requires 3 people.

 

[kjv1611]

Hmm, maybe this is where I was missing it... you're saying Sheet1 (If that's the real name, I'd give it a meaningful name for ease of reference) shows the capability of the machines, and that Sheet2 shows the capabilities of the machines. But in your examples, now it SOUNDS like you really mean that Sheet1 is what is actually produced, or what needs to be produced, rather than what the machines are CAPABLE of, and that Sheet2 is indeed showing what the machines are CAPABLE of.

So, is my assumption here correct, or am I still missing something?

 

[wayneryeven]

Your spot on.

Sheet1 is what we PLAN to produce.
Sheet2 is what the CAPABILITIES are (or constraints).

 

[SkipVought]

Hi,

Rather than operating by tying one hand behind your back, covering one eye, and hopping on one foot, parse the column using Data > Text to columns -- DELIMITED (using DASH and SPACE as delimiters)

Now that you have ALL your data in proper tabluar format, you can use Excel as it was designed to work with lookup functions.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kjv1611]

Okay, now that the logic is strait (for my understanding at least), I'll have to do some thinking about it, and possibly testing. But at the moment, I've got to get to other matters.. In the mean time, I wouldn't be shocked if someone posted the A.B.C. solution.

 

[AnotherHiggins]

wayner1980 ,

I'm about to run out, but give this a shot:

[COLOR=blue white]=INDEX(OFFSET(INDIRECT("Sheet2!$C$" & MATCH(A2, Sheet2!A:A, 0)), 0, 0, COUNTIF(Sheet2!A:A, A2), 1), MATCH(B2, OFFSET(INDIRECT("Sheet2!$B$" & MATCH(A2, Sheet2!A:A, 0)), 0, 0, COUNTIF(Sheet2!A:A, A2), 1), -1))[/color]

IMPORTANT: Make sure that Sheet2 is sorted by Machine first, then by Output descending. The match won't work if Output isn't sorted descending.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.