• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple criteria searching

aamirsq

Member
Hi,I was trying to make a selection based on multiple criteria but unable find some solution..


I want in H4 ratting based on my criteria on other sheet.


http://www.fileconvoy.com/dfl.php?id=g2c33111cc4afabed99930038124aa95e869bc63d7


Thanks
 
Hi, aamirsq!

I don't fully understand your requirement, would you please elaborate and post how should be applied the criteria on 2nd worksheet? Please post manually the output data wanted and how have you reached it.

Regards!
 
2nd sheet is my criteria. I want on i sheet1,

H4=3+5+1+5 How ?


3 points bcoz values b'w 5M-10M

5 points bcoz its Location in A1 (btw East/North = A1, South=A2 & West=A3)

1 point bcoz contractor is C.

5 points bcoz its Type 1


i hope u get it..
 
Hi Aamir,


So, if location is A1.. then why you are calculating 5 and not 3.

why A1 is always EAST never NORTH...

or how will you distinguis A1 = North or East..


whats problem with normal Index-match & sum..


=INDEX(Criteria!$B$1:$F$1,MATCH($B4,Criteria!$B$4:$F$4,0)) +

INDEX(Criteria!$B$1:$F$1,MATCH(Sheet1!$C4,Criteria!$B$5:$F$5,0)) +

.......


Regards,

Deb
 
Hi aamirsq,


Your question i still confusing, If you sort you on Sheet 1 it will look like this:

[pre]
Code:
Contractor	Type	Location	Value	Rating
A	        Type 4	A3	 15,024,072 	4
A	        Type 1	A1	 20,000,000 	4
A	        Type 3	A2	 500,000 	1
A	        Type 3	A1	 1,000,000 	2
[/pre]

Now how will you go with this? For Contr. A you have two type 3?


Regards
 
Sorry for late reply, you have a very goood point there its the distance from main store, which is located in EAST. which matters.


& for how will you distinguis A1 = North or East.. be i was referring to google map :) but just for now u think its just east/west/north/south later on i will dig the old files to use the old as reference :)


u made me think more!! nice ..
 
Hi Aamir!


Thanks for the feedback.


https://dl.dropboxusercontent.com/u/78831150/Excel/Multiple%20criteria%20searching%20%28aamirsq%29.xlsx


Please check the attached file..


I have changed lil bit in your Criteria Section.. and let us know the feedback..


Regards,

Deb
 
Back
Top