1

I am trying to set up an Excel formula using an index match function, where the lookup array can be in two different columns using structural referencing.

For some reasons I always get an error message on the lookup_array, which is referring to two table columns.

My formula is the following:

{=INDEX(Table1[Sales],MATCH(G4,Table1[Product],0),MATCH(H4,Table1[[Region 1]:[Region 2]],0))}

Hope someone can help me here. See also screenshot for an example case.

Excel example - screenshot

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Florentine
  • 15
  • 4
  • Match will only allow a 1 dimension range reference, either one column or one row. You cannot use two columns. This has nothing to do with Structured references, but how you are using MATCH incorrectly. – Scott Craner Mar 08 '23 at 15:21
  • Plus, the third criterion in INDEX is not a secondary Row check. See here on how to do multiple MATCH returns: https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another/42493697#42493697 – Scott Craner Mar 08 '23 at 15:24

1 Answers1

2

INDEX definition is: INDEX(Range,RowRef,ColumnRef) You are trying to use it as INDEX(Range,FirstRowRef,SecondRowRef) That will not work.

Also MATCH only allows a single Row or Column and cannot look up a match in multiple columns.

In this case with office 365 I would use FILTER:

=FILTER(Table1[Sales],(Table1[Product]=G4)*((Table1[Region 1]=H4)+(Table1[Region 2]=H4)),"")

That will return every one that matches the criteria. If you only want the first then wrap it in TAKE(...,,1)

=TAKE(FILTER(Table1[Sales],(Table1[Product]=G4)*((Table1[Region 1]=H4)+(Table1[Region 2]=H4)),""),,1)

If one does not have OFFICE 365 then we can use AGGREGATE instead of MATCH

=INDEX(Table1[Sales],AGGREGATE(15,7,(ROW(Table1[Product])-MIN(ROW(Table1[Product]))+1)/((Table1[Product]=G4)*((Table1[Region 1]=H4)+(Table1[Region 2]=H4))),1))
Scott Craner
  • 148,073
  • 10
  • 49
  • 81