Expert formula help needed

mistahmistah

New Member
Joined
May 8, 2014
Messages
21
Hello,

I need to create a formula to search for three fields of criteria A1 "Year" A2 "Month" A3"Status" then pull the A4 "Approvals" to a different summary spreadsheet. Keep in mind the number that I need will actually be in column B, but the table descriptions of data are placed in column A. Please use the below as an example, but I would like a dynamic formula to be able to extend beyond being specific to location as next month column B will be "June."

[TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]2014[/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]May[/TD]
[/TR]
[TR]
[TD]Status[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]Approvals[/TD]
[TD]125[/TD]
[/TR]
</tbody>[/TABLE]


Thank you!

Hello,

I need to create a formula to search for three fields of criteria A1 "Year" A2 "Month" A3"Status" then pull the A4 "Approvals" number to a different summary spreadsheet. Please use the below as an example, but I would like a dynamic formula to be able to extend beyond being specific to location as next month column B will be "June."

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]2014[/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]May[/TD]
[/TR]
[TR]
[TD]Status[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]Approvals[/TD]
[TD]125[/TD]
[/TR]
</tbody>[/TABLE]



Thank you!
 
Last edited by a moderator:
Re: Formula help needed

Would you also post a small snippet where these values can be found and the associated value for approvals can be retrieved?
 
Upvote 0
Re: Formula help needed

A pivot table won't do what I need. I want to be able to continually place new data into my working spreadsheet while my summary spreadsheet carries over these values as the columns move further to the right as new data is implemented automatically.

I'm new to Mr. Excel, how would I attach my file?
 
Upvote 0
Re: Formula help needed

[TABLE="width: 665"]
<colgroup><col span="7"></colgroup><tbody>[TR]
[TD]Year[/TD]
[TD]2014[/TD]
[TD]2014[/TD]
[TD]2014[/TD]
[TD]2014[/TD]
[TD]2014[/TD]
[TD]2014[/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]May[/TD]
[TD]May[/TD]
[TD]May[/TD]
[TD]Apr[/TD]
[TD]Apr[/TD]
[TD]Apr[/TD]
[/TR]
[TR]
[TD]Status[/TD]
[TD]Approved[/TD]
[TD]Average Income[/TD]
[TD]First Deposit[/TD]
[TD]Approved[/TD]
[TD]Average Income[/TD]
[TD]First Deposit[/TD]
[/TR]
[TR]
[TD]Approvals[/TD]
[TD="align: right"]478[/TD]
[TD]-[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2,870[/TD]
[TD]-[/TD]
[TD="align: right"]1,463[/TD]
[/TR]
[TR]
[TD]Active[/TD]
[TD="align: right"]400[/TD]
[TD]-[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2,715[/TD]
[TD]-[/TD]
[TD="align: right"]1,434[/TD]
[/TR]
[TR]
[TD]Cancelled[/TD]
[TD="align: right"]2[/TD]
[TD]-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]81[/TD]
[TD]-[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Closing[/TD]
[TD="align: right"]8[/TD]
[TD]-[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]37[/TD]
[TD]-[/TD]
[TD="align: right"]10

[/TD]
[/TR]
</tbody>[/TABLE]

The end goal is for a query to search Year, Month and Status then pull the value of approvals under 2014, May, Approved or 478 to a new sheet. But once I begin to add next month's data May will shift three columns to the right where April currently is. I want a formula on another spreadsheet to search the above criteria and put the value or 478 in the same cell although it will continually shift over to the right on the working spreadsheet.
 
Upvote 0
Re: Formula help needed

Below is a snippet of the summary page
[TABLE="width: 220"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"]May-14[/TD]
[TD="align: right"]Apr-14[/TD]
[/TR]
[TR]
[TD]Approved[/TD]
[TD] 478[/TD]
[TD] 2,870[/TD]
[/TR]
[TR]
[TD]First Deposit[/TD]
[TD] 3[/TD]
[TD] 1,463[/TD]
[/TR]
[TR]
[TD]Activation Rate[/TD]
[TD="align: right"]0.6%[/TD]
[TD="align: right"]51.0%[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
[TABLE="width: 665"]
<TBODY>[TR]
[TD]Year
[/TD]
[TD]2014
[/TD]
[TD]2014
[/TD]
[TD]2014
[/TD]
[TD]2014
[/TD]
[TD]2014
[/TD]
[TD]2014
[/TD]
[/TR]
[TR]
[TD]Month
[/TD]
[TD]May
[/TD]
[TD]May
[/TD]
[TD]May
[/TD]
[TD]Apr
[/TD]
[TD]Apr
[/TD]
[TD]Apr
[/TD]
[/TR]
[TR]
[TD]Status
[/TD]
[TD]Approved
[/TD]
[TD]Average Income
[/TD]
[TD]First Deposit
[/TD]
[TD]Approved
[/TD]
[TD]Average Income
[/TD]
[TD]First Deposit
[/TD]
[/TR]
[TR]
[TD]Approvals
[/TD]
[TD="align: right"]478
[/TD]
[TD]-
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]2,870
[/TD]
[TD]-
[/TD]
[TD="align: right"]1,463
[/TD]
[/TR]
[TR]
[TD]Active
[/TD]
[TD="align: right"]400
[/TD]
[TD]-
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2,715
[/TD]
[TD]-
[/TD]
[TD="align: right"]1,434
[/TD]
[/TR]
[TR]
[TD]Cancelled
[/TD]
[TD="align: right"]2
[/TD]
[TD]-
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]81
[/TD]
[TD]-
[/TD]
[TD="align: right"]12
[/TD]
[/TR]
[TR]
[TD]Closing
[/TD]
[TD="align: right"]8
[/TD]
[TD]-
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]37
[/TD]
[TD]-
[/TD]
[TD="align: right"]10

[/TD]
[/TR]
</TBODY>[/TABLE]

The end goal is for a query to search Year, Month and Status then pull the value of approvals under 2014, May, Approved or 478 to a new sheet. But once I begin to add next month's data May will shift three columns to the right where April currently is. I want a formula on another spreadsheet to search the above criteria and put the value or 478 in the same cell although it will continually shift over to the right on the working spreadsheet.

Hello,

I need to create a formula to search for three fields of criteria A1 "Year" A2 "Month" A3"Status" then pull the A4 "Approvals" to a different summary spreadsheet. Keep in mind the number that I need will actually be in column B, but the table descriptions of data are placed in column A. Please use the below as an example, but I would like a dynamic formula to be able to extend beyond being specific to location as next month column B will be "June."

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Year
[/TD]
[TD]2014
[/TD]
[/TR]
[TR]
[TD]Month
[/TD]
[TD]May
[/TD]
[/TR]
[TR]
[TD]Status
[/TD]
[TD]Approved
[/TD]
[/TR]
[TR]
[TD]Approvals
[/TD]
[TD]125
[/TD]
[/TR]
</TBODY>[/TABLE]

...

Let Sheet1, A:G house the data and Sheet2, A:B, the processing...

Sheet2, B4, control+shift+enter, not just enter:
Rich (BB code):
=INDEX(Sheet1!$B$4:$G$7,MATCH(A4,Sheet1!$A$4:$A$7,0),
  MATCH(1,IF(Sheet1!$B$1:$G$1=B1,IF(Sheet1!$B$2:$G$2=B2,
  IF(Sheet1!$B$3:$G$3=B3,1))),0))
 
Upvote 0
=INDEX(Portfolio!$B$4:$G$7,MATCH(A4,Portfolio!$A$4:$A$7,0),
MATCH(1,IF(Portfolio!$B$1:$G$1=B1,IF(Portfolio!$B$2:$G$2=B2,
IF(Portfolio!$B$3:$G$3=B3,1))),0))

Sheet 2 is called Summary

Didn't work. Still get #N/A with the array formula. I even copied over the format of Sheet 1 to Sheet 2, which is not how it was initially

[TABLE="width: 380"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]Year[/TD]
[TD]2014[/TD]
[TD]2014[/TD]
[TD]2014[/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]May[/TD]
[TD]May[/TD]
[TD]May[/TD]
[/TR]
[TR]
[TD]Status[/TD]
[TD]Approved[/TD]
[TD]Average Income[/TD]
[TD]First Deposit[/TD]
[/TR]
[TR]
[TD]Approvals[/TD]
[TD="align: right"]478[/TD]
[TD]-[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
=INDEX(Portfolio!$B$4:$G$7,MATCH(A4,Portfolio!$A$4:$A$7,0),
MATCH(1,IF(Portfolio!$B$1:$G$1=B1,IF(Portfolio!$B$2:$G$2=B2,
IF(Portfolio!$B$3:$G$3=B3,1))),0))

Sheet 2 is called Summary

Didn't work. Still get #N/A with the array formula. I even copied over the format of Sheet 1 to Sheet 2, which is not how it was initially

[TABLE="width: 380"]
<TBODY>[TR]
[TD]Year
[/TD]
[TD]2014
[/TD]
[TD]2014
[/TD]
[TD]2014
[/TD]
[/TR]
[TR]
[TD]Month
[/TD]
[TD]May
[/TD]
[TD]May
[/TD]
[TD]May
[/TD]
[/TR]
[TR]
[TD]Status
[/TD]
[TD]Approved
[/TD]
[TD]Average Income
[/TD]
[TD]First Deposit
[/TD]
[/TR]
[TR]
[TD]Approvals
[/TD]
[TD="align: right"]478
[/TD]
[TD]-
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
</TBODY>[/TABLE]

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key. When doen properly, a pair of { and } appears around the formula on the formula bar.

Here is the workbook showing the set up already described above:

https://dl.dropboxusercontent.com/u/65698317/mistahmistah%20Multiconditional%20IndexMatch.xlsx
 
Upvote 0
I'm aware of how to use array formulas.

I had some formatting inconsistencies between what you have and what I have, but i was able to edit it and make it work. Thank you very much Aladin!
 
Upvote 0

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