Excel Multiple Column Filter problem !!

[Balaji Venktesh]

Hi All,

I have an Excel sheet containing Employee Details. I need to filter out based on below mentioned criteria in multiple columns and find out respective employee name using only Formulas. Kindly help on this

"Find the person in the data sheet whose location is Bangalore, is part of the finance department, salary > 50000 and has a rating >=3"

Below is the screenshot of the final data.


Regards,
Balaji Venktesh

 

[PCosta87]

Hi All,

I have an Excel sheet containing Employee Details. I need to filter out based on below mentioned criteria in multiple columns and find out respective employee name using only Formulas. Kindly help on this

"Find the person in the data sheet whose location is Bangalore, is part of the finance department, salary > 50000 and has a rating >=3"

Below is the screenshot of the final data.
View attachment 37235

Regards,
Balaji Venktesh

Hi,

There are probably many other ways of going about doing this but the one in the attachment is the first that came to mind

Hope it helps

 

[PCosta87]

If you need to filter the info in place, then I would suggest using advanced filters

Please refer to attached file
Advanced filter using "L1:O2" as criteria.

 

[Balaji Venktesh]

Hi,

There are probably many other ways of going about doing this but the one in the attachment is the first that came to mind

Hope it helps

Thanks so much for solving this. Really helped me a lot. If possible, let me know others ways to achieve the same objective using only excel formulas....

 

[PCosta87]

Thanks so much for solving this. Really helped me a lot. If possible, let me know others ways to achieve the same objective using only excel formulas....

You are welcome
Will post other solutions if I can think of any

 

[Balaji Venktesh]

You are welcome
Will post other solutions if I can think of any

One thing i noticed, that you have used sumif formula to get S.No. But in my given data sheet, I dont have S.No. Still is there any way to come up with above result using excel formulas ?

Thanks for your support !!

 

[PCosta87]

One thing i noticed, that you have used sumif formula to get S.No. But in my given data sheet, I dont have S.No. Still is there any way to come up with above result using excel formulas ?

Thanks for your support !!

Hi,

If you can't add the S.No. to the original table I would suggest the other solution I posted, which uses advanced filtering (but no formulas).

If none of the previous 2 options is acceptable, then I can only think of INDEX/MATCH in an array formula, in order to use multiple criteria.
There are several problems with this approach however... Exact match is a no go since you need to look for compensation >50000 and rating >=3. This leaves only the other 2 match options that require the data to be sorted. This would be Ok for a single column but since you are sorting with multiple criteria and multiple columns there is no guaranty you would be able to sort all columns the same way, which would compromise the result

I am afraid I don't know how to do this any other way besides the 2 solutions already provided

 

[Balaji Venktesh]

Hi PCosta,

Atlast , i found the solution. Below link will provide you the solution

http://spreadsheeto.com/index-match/

 

[PCosta87]

Hi PCosta,

Atlast , i found the solution. Below link will provide you the solution

http://spreadsheeto.com/index-match/

Great
I will take a look at it.
Many thanks for letting me know.