Menu
Basically, I would like Sheet 1 to pull data FROM Sheet 2, renamed to 'Loot'. However, the data that is pulled from 'Loot' can only be from a specific column, and two other cells in the same row must contain specific data from Sheet 1.
Example: 'Loot' has the following important Columns for B, C, and D: Character, Item Type, and Quantity
I want a cell in Sheet 1 to contain a Quantity(D) value from 'Loot', but only if the Character(B) and Item Type(C) cells that share the same row as the data that is being pulled matches that of two specific cells on Sheet 1.
This is as close as I could come without success.
=QUERY('Loot'!D2:D;"select * where (B matches A14 and C matches A8)";1)
Here is a temporary link to the spreadsheet. https://docs.google.com/spreadsheet/ccc?key=0AgUUnVW8yWhXdHhsZnpac29LdjdhTG9adFg4NnhwVHc&usp=sharing
Sheet 1 is Veldspar
Sheet 2 is Loot
With the help of a friend, I'be been able to get this far...
=QUERY('Loot'!B2:D100,"select (D) where B = '" & A14 & "' and C = '" & A8 &"'")
The problem is that if there is more than one result, instead of adding it together, it just displays each result in each cell below it
EDIT
Fixed all the issues and found another one.
=SUM(QUERY('Loot'!B$2:D,"select (D) where B matches '"& $A14 &"' and C matches '" & A$8 &"'"))
The problem now is that if there is no criteria to pull data from, the cell produces a #N/A error which causes all other cells that reference its data to be unable to calculate their data.
EDIT..
So far so good, this is what I used to fix the issue
=IFERROR(SUM(QUERY('Loot'!B$2:D,"select (D) where B matches '"& $A14 &"' and C matches '" & A$8 &"'")),"0")
EDIT..
That last one edit the trick. My problem has been solved. I left the link available to all and just changed all the names located in the Loot 'sheet' in order to hide names.
878
And it is Google Sheets INDEX MATCH that supports multiple criteria, not VLOOKUP. Here's the formula you will need to use: =ArrayFormula (INDEX (B2:B24, MATCH (CONCATENATE (F2:F4), A2:A24&C2:C24&D2:D24, 0),))
Perhaps the most powerful use of MATCH in Google Sheets is when you use it in combination with the INDEX function, in order to look up values. But there’s already a VLOOKUP function in Google Sheets for that purpose, right? True — but the combination of MATCH and INDEX can solve several problems that arise when using VLOOKUP.
In Google Sheets, there is a better solution. Without inserting any additional column you can use VLOOKUP in Google Sheets for multiple criteria VLOOKUP. VLOOKUP with Multiple Criteria in Google Sheets Using ArrayFormula This is the recommended method to deal with multiple criteria in Google Sheets.
column, just like row, is the number of offset columns. Also optional, also 0 if omitted. If you specify both optional arguments (row and column), Google Sheets INDEX will return a record from a destination cell: Skip one of those arguments and the function will get you the entire row or column accordingly:
Answering my own question :)
=IFERROR(SUM(QUERY('Loot'!B$2:D,"select (D) where B matches '"& $A14 &"' and C matches '" & A$8 &"'")),"0")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With