erniepoe

Active Member
Joined
Oct 23, 2006
Messages
339
Office Version
  1. 365
Platform
  1. Windows
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))
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
339
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

Active Member
Joined
Oct 23, 2006
Messages
339
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,171,631
Messages
5,876,554
Members
433,200
Latest member
CoolBlue_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top