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

VLookup for a list of names with two sets of values

zacpower

New Member
I am trying to use VLookup to sort data based on two value criteria. I've tried Lookup, VLookup, Index/Match without any luck.


If this is my data, I need to sort these names using the following criteria:


Really Bad: Score = 0-7 and Gap <10%

Sort of Bad: Score = 0-7 and Gap >-10% <+10%

Average: Score = 8-15 and Gap >-10% <+10%

Really Good: Score = 16-20 and Gap >20%


Name Score % Gap

Damon, Matt 15 -19%

Cruz, Penelope 13.5 -19%

Lohan, Lindsay 17.5 30%

Johnson, Jack 13 21%

Harper, Ben 13.5 -4%

Blanchett, Cate 15 -19%

Cruise, Tom 20 -5%

Osbourne, Ozzy 17.5 -7%

Gehry, Frank 9 3%

Obama, Barack 12 -17%

Urban, Keith 15 -7%


Thanks in advance for any advice/assistance!
 
ZacPower

I assume you have 3 columns with Name, Score and Gap


You could use a formula like this in D2 and copy down


Code:
=Case(IF(AND(B2<7,C2<0.1),1,IF(AND(B2>=0,B2<=7,C2>-0.1,C2<=0.1),2,IF(AND(B2>=8,B2<=15,C2>-0.1,C2<=0.1),3,IF(AND(B2>=16,B2<=20,C2>-0.2),4,5)))),"Really Bad","Sort of Bad","Average","Really Good","Unlisted" )


There appears to be a number of items though that don't match the 4 criteria you have listed as they have vaues outside the ranges listed ?

Namely, Damon, Cruz, Johnson, Blanchett and Obama
 
zacpower,


try this, for the given criteria: =IF(AND(B2>0;B2<=7;C2<10%);"Really Bad";IF(AND(B2>0;B2<=7;C2>-10%;C2<10%);"Sort of Bad";IF(AND(B2>=8;B2<=15;C2>-10%;C2<10%);"Average";IF(AND(B2>=16;B2<=20;C2>20%);"Really Good";"N/A"))))


I should warn you that this formula doesn't work for the score/gap combinations that don't meet your criteria, so the formula will evaluate to N/A. (For instance, in row 1, "Matt Damon" has a score of 15 - corresponding to "Average", but a gap of -19% - corresponding to "Really Bad"; the same situation is in rows 2, 4, 6, 7, 8, 10 of your data). So, what you could do is set up some intermediate criteria, or maybe modify these, so that the criteria cover the whole range of values.


Best regards,

Radu
 
Back
Top