Multi-Sheet, Multi-Table Vlookup possible?

Howdy,

I been scaling the internet trying to figure out a way that doesn't involve basic. Hopefully it's possible. I was goofing around when an excel ideal came into my mind, yeah pretty boring life. Wasn't sure if the following was possible?

Ideal based on a game where you build items depending on the resources available.

I have a list of requirements for each item. The goal of the table would be to enter in the current resource available, in this case wood, stone, iron and crops. Using this information, I would do either a vlookup or some other function to determine what the most of that resources i was able to make. Kinda of like a greatest common denominator type problem.

I've attached the file for you to see what I did. If it needs a complete re-engineering let me know. There are no macros, so if it ask you to allow macros, disasble, plus it's not saved as a macro enabled xls file.


Thanks for your help.

Not entirely sure I follow (expected results often help) but perhaps the below?

Please Login or Register  to view this content.

The above would return the most of any given type that could be produced given resources.

Note this uses your table for 1 unit - and your tables imply no economies of scale (ie no marginal costing to worry about)

If you want the best combination of all items that can be produced with the resources (ie the best mix of the four items) then I think you would need something much more sophisticated than the above
(and perhaps assigned a preference weighting to each item)

Originally Posted by cte

Let's say I have the following resources: Wood - 3820; Stone - 3200; Iron - 5880; and Crop - 1540
I want to find out what is the most I can get out of these resources. In this case, the formula (it may not be a vlookup) would return the following (Master!F4:Master!I4): Infantry - 31, Horses - 5, Archer - 12, Catapults - 2;

That's what the prior suggestion gives you....

For each "item" the formula conducts 4 calculations, 1 per resource.

The calculation divides the resource value by the appropriate per unit cost of that resource for that item

The resulting values are rounded down to the nearest whole number (via INT)

It follows that the most you can have of a given item is determined by the lowest whole number returned by the division calculations (ie the least whole units that can be generated given resourcing level and per unit cost)
As such we use MIN to pull the lowest of the 4 results.

If we use your sample file and look at Infantry - you have resource values of:

Please Login or Register  to view this content.

and the per unit cost for Infantry is

Please Login or Register  to view this content.

so if we divide the values as described we get:

Please Login or Register  to view this content.

which we round down (via INT)

Please Login or Register  to view this content.

we then apply MIN to the above array of values

Please Login or Register  to view this content.

and that number represents the most Infantry we can create given our resource values.