My IF, And, Relative Failure

[chegism]

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.

[K m]

You could use INDEX and MATCH

Howevr, do you have a subset of your data you can post?

[chegism]

Please Login or Register  to view this content.

Something like this.

If B* = 2008 And D* = "Mineral waxes nes", then G2 would be the result.

[K m]

It would be better if it were already in Excel format rather that text

[chegism]

Example.xlsx

Alright, so...

Sheet 1, F7. I need it to display 120474 from Sheet 3, G2. It needs to see that this data (120474) is from 2008 and for "Bitumen and asphalt, asphaltites and asphaltic rocks".

This is a very small section of the data. There are dozens of years and other countries will have all of the different energy types.

Sorry if this is difficult to understand, it's hard to explain.

[chegism]

You could use INDEX and MATCH

Howevr, do you have a subset of your data you can post?

I research this some more and was able to get my formula to do what I wanted with:

=INDEX(Sheet2!G2:G12000,MATCH(1,(Sheet2!A2:A12000="Anguilla")*(Sheet2!B2:B12000="2008")*(Sheet2!D2:D12000="Coal briquettes, ovoids, similar made solid fuels"),0))

I am trying to implement ISERROR to remove the N/A values that arise from some countries not having the same amount of data as others, but I cant seem to figure it out.

[Pete_UK]

You just put ISERROR around all of that, like this (in red):

=ISERROR(INDEX(Sheet2!G2:G12000,MATCH(1,(Sheet2!A2:A12000="Anguilla")*(Sheet2!B2:B12000="2008")*(Sheet2!D2:D12000="Coal briquettes, ovoids, similar made solid fuels"),0)),"")

Hope this helps.

Pete

[chegism]

You just put ISERROR around all of that, like this (in red):

=ISERROR(INDEX(Sheet2!G2:G12000,MATCH(1,(Sheet2!A2:A12000="Anguilla")*(Sheet2!B2:B12000="2008")*(Sheet2!D2:D12000="Coal briquettes, ovoids, similar made solid fuels"),0)),"")

Hope this helps.

Pete

That one says "You have entered too many arguments for this function" and highlights the double quotes at the end.

[vlady]

why not use IFERROR your in xl 2010 right?

[Pete_UK]

Sorry, it should be IFERROR:

=IFERROR(INDEX(Sheet2!G2:G12000,MATCH(1,(Sheet2!A2:A12000="Anguilla")*(Sheet2!B2:B12000="2008")*(Sheet2!D2:D12000="Coal briquettes, ovoids, similar made solid fuels"),0)),"")

Hope this helps.

Pete

[chegism]

why not use IFERROR your in xl 2010 right?

Changing IS to IF fixed it, thanks!

---------- Post added at 08:48 PM ---------- Previous post was at 08:47 PM ----------

Sorry, it should be IFERROR:

=IFERROR(INDEX(Sheet2!G2:G12000,MATCH(1,(Sheet2!A2:A12000="Anguilla")*(Sheet2!B2:B12000="2008")*(Sheet2!D2:D12000="Coal briquettes, ovoids, similar made solid fuels"),0)),"")

Hope this helps.

Pete

Thanks Pete.