Lookup with INDEX - variable array

[andrewc]

Hi,

Attached is a sample.

The formulae in column I are used to extract values from column C.

However, the column named "Value", currently column C, may be in another column next month so I'd like the formulae in column I to be able to accommodate this.

Thanks!

[Richard Buttrey]

Please upload the workbook and manually add the results you expect to see.

[andrewc]

Thanks Richard, I've edited the post.

Hopefully it's more clear!

Thanks

[Richard Buttrey]

Hi,

One way would be then following array formula in I2

=IF(ISBLANK(MATCH(G2&H2,$A$2:$A$7&$B$2:$B$7,0)),"n/a",INDEX($A$2:$E$7,MATCH(G2&H2,$A$2:$A$7&$B$2:$B$7,0),MATCH(I$1,$A$1:$E$1,FALSE)))

This will adapt to accommodate the varying position of the Value column. Just make sure the index range A:E in the formula is wide enough

However don't ignore the more obvious pivot table option. It took me about 30 seconds to create a pivot table of your data.

[GeneralDisarray]

Easy way, would be to use table objects.

See attached.

[andrewc]

Thanks very much!

Is someone able please to suggest a solution that doesn't use tables?

Thanks

[GeneralDisarray]

Try this formula:

=IF(COUNTIFS(A:A,G2,B:B,H2,C:C,"")>0,"n/a",SUMIFS(INDEX($1:$1048576,,COLUMN(INDEX($1:$1,MATCH("Value",$1:$1,0)))),A:A,G2,B:B,H2))


See 'Another way' on attached file.


p.s..... why avoid tables?

[newdoverman]

As long as the column with the values has "Values" as a header, this will accommodate the values being in different columns. Only one column header can have Values as the header.

=IFERROR(INDEX($A2:$E7,MATCH(G2&H2,$A$2:$A$7&$B$2:$B$7,0),MATCH("value",$A$1:$E$1,0)),"")

	A	B	C	D	E	F	G	H	I
1	Product	Country			Value		Product	Country	Value
2	AAA	Belgium			12		AAA	UK	8
3	BBB	Belgium			15		BBB	UK	0
4	CCC	Belgium			17		CCC	UK	0
5	AAA	UK			8
6	BBB	UK
7	CCC	UK			0

[GeneralDisarray]

To my point about tables - I realize that using the tables seems odd at first, as you don't absolutely need them to get by. HOWEVER the real usefulness of the tables is that anytime you need to reference the data they contain, anywhere in the project, you can do so by name. This is enormously helpful when you start designing more and more parts of the project, but it's really handy when you have to look over another persons design, or even your own work later on when it's not fresh in your mind.


NON-Table Formula -- It's not terrible, but it's a mouthful. If you read it out loud it means nothing (until you track down all the references).

=IF(COUNTIFS(A:A,G2,B:B,H2,C:C,"")>0,"n/a",SUMIFS(INDEX($1:$1048576,,COLUMN(INDEX($1:$1,MATCH("Value",$1:$1,0)))),A:A,G2,B:B,H2))

Table way -- Now, try reading this out loud... sounds better.

=IF(COUNTIFS(Table2[Product],G2,Table2[Country],H2,Table2[Value],"")>0,"n/a",SUMIFS(Table2[Value],Table2[Product],G2,Table2[Country],H2))

Now - Go 'whole hog' with it. Rename the tables, and use them in BOTH places (I mean in the summary and the dataset) NOW you can really see what you are doing when you read through it

=IF(COUNTIFS(T_RawData[Product],[@Product],T_RawData[Country],[@Country],T_RawData[Value],"")>0,"n/a",SUMIFS(T_RawData[Value],T_RawData[Product],[@Product],T_RawData[Country],[@Country]))

You only need to know a couple of key points: When you see a table name with a header in brackets like this, T_RawData[Product] , you are referencing the entire data column under the header. WHen you see the @ symbol inside the brackets, you are referencing the value under the header on the same row as the formula cell (so, just that one row - not the whole set).

:D

[andrewc]

Thank you all - very helpful!

[newdoverman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.