Looking up data and providing the difference plus a word

**Gmunro** · 06-15-2012, 04:09 AM

Hi All

I have enclosed a spread sheet as a sample to my issue with a few examples on it.

I am trying to reference a start point against a finish point and make it appear as a word and number ie.

Range - 3c/3b/3a/4c/4b/4a/5c..... etc. Each sub letter counts as 1

Target 3a - if they get 3a, I wish it to say 'achieved'
target 3a - if they get 4b, I wish it to say 'above (2)'
Target 3a - if they get 3c, I wish it to say 'below (2)'

Hope this is decent explanation, I hope someone can help me, many thanks for looking

Graemebelow & above example.xlsx

**twiggywales** · 06-15-2012, 04:19 AM

this should be easy to do ill upload abook shortly but esecncally it willl use match and then values from that

**twiggywales** · 06-15-2012, 04:25 AM

here is the formula

Please Login or Register  to view this content.

fun

below & above example.xlsx

**martindwilson** · 06-15-2012, 04:29 AM

in d2
=IF(MATCH(C2,$F$2:$F$20,0)-MATCH(B2,$F$2:$F$20,0)=0,"achieved",IF(MATCH(C2,$F$2:$F$20,0)-MATCH(B2,$F$2:$F$20,0)<0,"below ("&ABS(MATCH(C2,$F$2:$F$20,0)-MATCH(B2,$F$2:$F$20,0))&")","above ("&MATCH(C2,$F$2:$F$20,0)-MATCH(B2,$F$2:$F$20,0)&")"))