This is quite the complex dilemma, so bear with me. I run a shop on a Pokemon fansite-type thing selling members of various Pokemon species that I’ve hunted, and I decided today to make a spreadsheet to better enable me to run that shop. The sheets I have made so far detail the current stock of my store, the order in which said stock should be displayed, the type distribution across my stock, and - the problem sheet - a list of future hunts to create future stock.
My Type Distribution page looks like this, currently. It totals the occurrence of each type across the previous page and then ranks them in descending order from highest quantity; said rank is hereafter called “Priority”.
Here, meanwhile, is my current Future Hunt Priorities set-up. What I would like to achieve is to match the types of each Pokemon in Columns B-D against Columns A-C in the previous sheet, and return the highest number from Column C Sheet 2 in Column E Sheet 3. For example, if a Pokemon covers the types Bug, Flying and Steel in its lifetime, I would like the Priority column to return the highest Priority in the previous sheet out of those types.
(Don’t worry, the ‘blank’ cells in Sheet 3 aren’t really blank; they’re filled with the word Null that happens to be in white text so it’s not an eyesore but it still counts as observable data. This is why Null appears in Sheet 2 as well.)
As you can see, I attempted to recreate a facsimile of the formula by using the Large function to select the highest of two types manually selected. While it patches the gaps theoretically, I don’t see it being practical in the long term, because my stock levels are subject to change, and thus priorities will shift accordingly on Sheet 2 and the data on Sheet 3 will thus no longer be accurate. The ideal formula for this situation would be one that works regardless of the order of types and will always return the result two columns to the right of the matching data regardless of which row it’s in, but thus far I haven’t been able to find a formula that functions like this. I’ve also attempted the OFFSET formula, to no success.
I also enclose a download link of the spreadsheet in question, to see if anyone working manually with it may be able to divulge better results: http://www.mediafire.com/file/9zz39ucvh3r2lyf/pokefarm_store_sheets.ods
Thank you in advance! This is my first post, so let me know if there’s anything I need to clear up.