Index Match

[erniepoe]

Hi All,

having some formula difficulties and hoping somebody may be able to assist. I am trying to find the corresponding value on the "total operating expenses" line on 'G+A' tab (column names are in column A, the data goes from columns B:AM), provided that the value in row 7 is "Company Inc." and the value in row 9 is the date (in this case 1/31/2018).

The answer for this particular cell would be found in column Z, for instance, but my formula is returning an error.

Can anybody see where I have gone wrong? Thanks in advance!




=INDEX('G+A'!B:AM,MATCH("Total Operating Expenses",'G+A'!A:A,1),MATCH("Company Inc.",'G+A'!B7:AM7,1),MATCH("1/31/2018",'G+A'!B9:AM9,1))

 

[erniepoe]

ok, it appears that this needs to be a CSE formula, so i have adjusted to as follows, but still not working. or perhaps was i correct the first time with the formula above??


=INDEX('G+A'!B:AM,MATCH("Total Operating Expenses",'G+A'!A:A,1),MATCH("Company Inc."&T3,'G+A'!B7:AM7&'G+A'!B9:AM9,0))

 

[James006]

Hello,

Your Index should refer to your whole range ...

and you should only have two Matches ...

the first Match to locate the row ... and the second Match to locate the column ...

Hope this will help

 

[erniepoe]

thank you James,

but i need it to locate two rows, as the data in row 7 doesnt always say "company inc" and the data in row 9 are dates, so in this case i need it to find 1/31/2018, so there would be two different criteria for two different row.

Best,
Ernie

 

[James006]

Hello Ernie,

Not quite sure to understand yours constraints ...

If you need to use Index Match with multiple criteria ... take a look at following explanation :

https://exceljet.net/formula/index-and-match-with-multiple-criteria

Hope this will help