Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have this spreadsheet that started as a silly aid for a game (Mafia Wars 2), but grew into a tricky spreadsheet question.

In the game your character have 9 "slots" for weapons and armors, 1 for each "type": Light Weapon, Heavy Weapon, Body Armor, Head Armor, etc. So I made a list of all weapons and armors available in the game, 1 item per row. Example:

SHOP        ITEM TYPE       ITEM NAME       ATK DEF PRICE   EQUIPPED?
Marketplace Weapon Light    Konrad Knife    16  5   5.500
Marketplace Weapon Light    Ice Queen       19  6   8.200
Marketplace Armor Body Up   Layered Polym   0   31  8.600
Marketplace Armor Body Up   Full Shield     7   42  17.650
Marketplace Weapon Heavy    Konrad Bullpup  53  25  24.500
Marketplace Weapon Heavy    Full Moon Blow  73  12  24.500  x
Marketplace Armor Body Low  Knee Pads       17  26  14.200  x
Marketplace Armor Body Low  Army Boots      15  55  24.500
Bone Yard   Weapon Light    Bone Launcher   41  2   9.400   x   
Neon Strip  Vehicle Ground  Supercharged    41  34  24.500
Dead End    Weapon Heavy    Sharp Sickle    21  5   24.500
Dead End    Armor Body Low  Unholy Boots    5   36  15.000
Dead End    Armor Head      Hockey Mask     5   18  15.900  x

Last columns is an indication of the items i have already bought and equipped (marked with "x"). What I need is a formula that, for each "slot" (item type), returns info related to the item of that kind that I am using. That would be:

ITEM TYPE       SHOP NAME   ITEM NAME       ATK DEF PRICE
Weapon Light    Bone Yard   Bone Launcher   41  2   9.400
Weapon Heavy    Marketplace Full Moon Blow  73  12  24.500
Weapon Special  --          --              --  --  --
Armor Body Up   --          --              --  --  --
Armor Body Low  Marketplace Knee Pads       17  26  14.200
Armor Head      Dead End    Hockey Mask     5   18  15.900
Vehicle Ground  --          --              --  --  --
Vehicle Water   --          --              --  --  --
Vehicle Air     --          --              --  --  --

The item types are fixed, so they can be hard coded. Each row for an item type.

So, for 1st result line, it would return data from the row where both 2nd column is "Weapon Light" and last column is "x". Basically I need a LOOKUP (or VLOOKUP, or anything else) that uses 2 criteria to find a given row, the item type and the X marker.

Question is: HOW?

I am using OpenOffice Calc 3.2.1, but since it shares so many functions with MS Excel, answers for Excel are also fine (as long as it only uses regular formulas, no VBScript or Macros or VBA etc)

Last but not least, suggestions / solutions for rearranging the data so it makes this problem easier to solve are also welcome.

Thanks!

share|improve this question

1 Answer 1

Here's how to look up the shop name for each item in your table. If the first table is found at A1:G14, and the second table you are filling out is found at K1:Q10, you can use the following formula in L2 to look up the store name of the equipped item of type in K2:

=IFERROR(INDEX($A$1:$A$14,MIN(IF($B$2:$B$14=K2,IF($G$2:$G$14="x",ROW($B$2:$B$14),1000000),1000000))),"--")

Enter the formula as an array formula by pressing Ctrl+Shift+Enter. If done properly, the formula will appear encased in {=...}. This formula can be filled down the column.

To adapt this formula to fill out the other columns, you must change the first argument of the INDEX formula to the appropriate column in the first table. For example, to find the Item name, you would use $C$1:$C$14 as the first argument of the INDEX formula.

EDIT:

Well, I just checked OpenOffice Calc, and unfortunately it does not support IFERROR. So here's a longer, more awkward version of the formula that will work in OO Calc:

=IF(ISERROR(INDEX($A$1:$A$14;MIN(IF($B$2:$B$14=K2;IF($G$2:$G$14="x";ROW($B$2:$B$14);1000000);1000000))));"--";INDEX($A$1:$A$14;MIN(IF($B$2:$B$14=K2;IF($G$2:$G$14="x";ROW($B$2:$B$14);1000000);1000000))))

This also must be entered as an array formula by pressing Ctrl+Shift+Enter. Beware that you have to adapt both INDEX formulas when using this in other columns.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.