I have an Excel formula I'm having trouble with. I have searched for an answer on Google and YouTube but can't seem to find one.
In Excel I'm using an aggregate function with two criteria. Based on "Location" and "Function" I'm able to extract records from endless rows and columns of data.
I have a dropdown list for both Location and Function. For "Location" I want to be able to choose between:
No Selection, Amsterdam, Paris, London and Madrid.
For "Function" I want to be able to choose between:
No Selection, Banker, Banker II and Assistant.
I'm using the following formula:
=IF(ROWS(M$2:M2)>$I$6;"";INDEX(source[Name];AGGREGATE(15;6;(ROW(source[Name])-ROW(source[#Headers]))/((source[Location]=$I$2)*(source[Function]=$I$3));ROWS(M$2:M2))))
I want to be able to extract data based on two criteria but also based on one criteria. That's why I'm using "No Selection". No whenever I choose "No Selection" for Location, I want to be able to extract the data for all the bankers in all of the locations.
The problem I'm having is with the above formula I have to choose two criteria. Whenever I select "No Selection" on one of the dropdown lists it wont extract data.
I know an IF function can be used but I can't get it right. It's a school project I'm working on and my knowledge about Excel is very limited.
Link to an example I'm working with: https://www.dropbox.com/s/mhsfzv7d63y7rb7/example%281%29.xlsx?dl=0
On your sample sheet:
M2: =IFERROR(INDEX(source,AGGREGATE(15,6,1/(((source[[Location]:[Location]]=$I$2)+($I$2="No Selection"))*((source[[Function]:[Function]]=$I$3)+($I$3="No Selection")))*(ROW(source)-ROW(source[#Headers])),ROWS($1:1)),COLUMNS($A:A)),"")
Select M2 and fill down to M14
Select M2:M14 and fill right.
Note that the addition operator is mimicking the IF function for each entry in the array. i.e. ((source[[Location]:[Location]]=$I$2)+($I$2="No Selection")) will return a 1 if Location = either the name in the drop-down, OR if the dropdown = No Selection