Expert formula help needed

[mistahmistah]

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."

Year	2014
Month	May
Status	Approved
Approvals	125

<tbody>
</tbody>

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."

Year	2014
Month	May
Status	Approved
Approvals	125

<tbody>
</tbody>

Thank you!

 

[bschwartz]

Have you considered using a PivotTable?

 

[Aladin Akyurek]

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?

 

[mistahmistah]

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?

 

[mistahmistah]

Re: Formula help needed

Year	2014	2014	2014	2014	2014	2014
Month	May	May	May	Apr	Apr	Apr
Status	Approved	Average Income	First Deposit	Approved	Average Income	First Deposit
Approvals	478	-	3	2,870	-	1,463
Active	400	-	2	2,715	-	1,434
Cancelled	2	-	0	81	-	12
Closing	8	-	1	37	-	10

<colgroup><col span="7"></colgroup><tbody>
</tbody>

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.

 

[mistahmistah]

Re: Formula help needed

Below is a snippet of the summary page

	May-14	Apr-14
Approved	478	2,870
First Deposit	3	1,463
Activation Rate	0.6%	51.0%

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Year	2014	2014	2014	2014	2014	2014
Month	May	May	May	Apr	Apr	Apr
Status	Approved	Average Income	First Deposit	Approved	Average Income	First Deposit
Approvals	478	-	3	2,870	-	1,463
Active	400	-	2	2,715	-	1,434
Cancelled	2	-		81	-	12
Closing	8	-	1	37	-	10

<TBODY>
</TBODY>

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."

Year	2014
Month	May
Status	Approved
Approvals	125

<TBODY>
</TBODY>

...

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

Sheet2, B4, control+shift+enter, not just enter:

=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))

 

[mistahmistah]

=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

Year	2014	2014	2014
Month	May	May	May
Status	Approved	Average Income	First Deposit
Approvals	478	-	3

<colgroup><col span="4"></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

=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

Year	2014	2014	2014
Month	May	May	May
Status	Approved	Average Income	First Deposit
Approvals	478	-	3

<TBODY>
</TBODY>

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

 

[mistahmistah]

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!