I have 1 sheet with 11000 rows of data, most of which I don't need.

Column B contains years (2006, 2007, 2008 etc), while Column D contains a large text string. Column G contains a weight in kilograms.

I am trying to reference this sheet to pull out the data I need (kg).

Here is my failed attempt at a formula to do so:

=IF(AND(Sheet3!B$1=2007, Sheet3!D$1="Coal"),Sheet3!G$1,"Nothing")

Each year will have a different text string and kg amount in it's row. I'm simply trying to make a formula that states if the row contains 2007 AND Coal, use that row's KG value. This formula works:

=IF(AND(Sheet3!B26=2007, Sheet3!D26="Coal"),Sheet3!G26,"Nothing")

But I need Excel to automatically find the rows and data for me.