How to set 2 conditions / criterias for VLOOKUP / LOOKUP / etc in OpenOffice Calc (or Excel)
- by MestreLion
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!
