vlookup error even though data available

  • Thread starter Thread starter Jerry
  • Start date Start date
J

Jerry

I have the following function
=LOOKUP(F11,{"A+","B+","C+","D+","A-","B-","C-","D-","A","B","C","D","F"},{100,85,70,55,90,75,60,45,95,80,65,50,40})
and when say cell F11 = A it returns #N/A even though the equivalent should
be 90 or if F11 = B or B- returns 100, is there anything wrong with this
formula. Thanks in advance
 
The lookup_vector *must* be sorted in ascending order. Try it like this:

=LOOKUP(F11,{"A","A-","A+","B","B-","B+","C","C-","C+","D","D-","D+","F"},{95,90,100,80,75,85,65,60,70,50,45,55,40})
 
thanks a lot it did work. I have this formula and I would like to only be
true if the conditions are met, for instance if p2>r2+6 then returns the
value of increase, under 6+ decrease otherwise no change.
=IF(ISERROR(MATCH(P2,R2,0)),IF(P2>R2+5,"Decreased","Increased"),"No Change")
This formula only works even if p2>r2 regardless but not how I want my
response
 
If I understand your requirements, try this:

=IF(COUNT(P2,R2)=2,IF(P2=R2,"No
change",IF(P2>R2+6,"Increase","Decrease")),"")
 
Back
Top