Index Match for 2 columns and 1 row

p9j123

Active Member
Joined
Apr 15, 2014
Messages
288
Office Version
  1. 2013
Platform
  1. Windows
I have the following table below, I need help in creating a formula in B4 that will satisfy the Requirements in B1, B2 and B3, on the illustration below, the expected result is Q2

This is my initial formula that is giving an #N/A result
=INDEX(D8:E11, MATCH(1, (B8:B11="Apple") * (C8:C11="Salary") * (D7:E7=G1), 0))

Book1
ABCDE
1CompanyApple
2ItemSalary
3MonthFeb
4Result#N/A
5
6
7CompanyItemsJanFeb
8AppleSalaryQ1Q2
9AppleBalanceQ3Q4
10SamsungSalaryQ5Q6
11KinkosBalanceQ7Q8
Sheet1
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
How about
Fluff.xlsm
ABCDE
1CompanyApple
2ItemSalary
3MonthFeb
4ResultQ2
5
6
7CompanyItemsJanFeb
8AppleSalaryQ1Q2
9AppleBalanceQ3Q4
10SamsungSalaryQ5Q6
11KinkosBalanceQ7Q8
Sheet2
Cell Formulas
RangeFormula
B4B4=INDEX(D8:E11,MATCH(1,(B8:B11=B1)*(C8:C11=B2),0),MATCH(B3,D7:E7,0))
 
Upvote 1
Solution
Thank you @Fluff it is working as expected when executed using ctrl+shift+enter
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,215,071
Messages
6,122,964
Members
449,094
Latest member
Anshu121

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
Back
Top