If(AND) formula, referring between tables

0
votes

I have a huge dataset which needs reformatting.

Currently it is like this;

enter image description here

But i need it to be like this;

enter image description here

I have got as far as this formula, which would be inserted into column 'Area A' of the second picture

=IF(AND(Table1[Cell ID]=Table2[Cell_ID],Table1[EUNIS_desc]=Table2[[#Headers],[Acid alpine, subalpine and extensive grassland]]),Table1[AREA],0)

However whilst this populates the first row correctly. All other blocks below in the same column just respond false giving "0" even when they should also have a true value.

Another problem is that the formula for some reason extends the table massively (down) with empty false values.

Kind Regards,

Harrison

1
excelexcel-formulavba
Index/Match would probably work better. - Scott Craner
Hi, ive lookedup Index Match functions and have come up with this formula. It looks like it could work, but at the moment is not. Currently the value returned is simply the first entry to the area collumn in table 1 for all rows below. Assume that "J2" represents the location of Cell id 1 in table 2. =INDEX(Table1[AREA],AND(MATCH(J2,Table1[CELL ID],0),MATCH(COVER A,Table1[EUNIS_desc],0))) - H.Tan
try this array formula: =INDEX(Table1[AREA],MATCH(1,IF((Table1[CELL ID]=j2,0)*(Table1[EUNIS_desc]="COVER A"),1,0),0))) . Being an array it needs to be confirmed with Ctrl-Shift-Enter instead of enter when exiting edit mode. - Scott Craner

1 Answers

1
votes

Assuming that "Cell ID" is in column A in Table1 then on cell B2 on Table2 you would place:

=SUMIFS(Table1!$C:$C,Table1!$A:$A,$A2,Table1!$B:$B,"Cover A")

You can extend this down to all "Cell ID"s and then copy to all types of covers just by changing the criteria2 in the SUMIFS to each different cover type (ie "Cover B" or "Cover C").