lookup

[fowler]

hi all i am usinga lookup formula and need help the forumula is at the momnet
=lookup(d5,{1,2,3,},{11,20,3,0})
this forumul wil have over 100 numbers in each array can anybody advise how to use cell ref in place of actual numbers

 

[jasonb75]

Type =LOOKUP into any cell then press f1 to open the help file.

 

[fowler]

hi thanks but dont give me the answer

 

[Aladin Akyurek]

hi thanks but dont give me the answer

Your question is not clear. Don't you have those numbers in ranges of their own?

 

[jasonb75]

What does it give you then?

I'm not trying to make it difficult for you, but to me it makes more sense to guide you through how to find this yourself then to give you the answer straight away, if you can find it in the help file then it will be quicker than waiting for somebody to answer, maybe not this time, but next time, and the time after, etc.

 

[fowler]

this is the formula so far using inbuilt help, as the number range can change, from 10 to 100+


=LOOKUP(D5,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108},{100,75,65,60,56,53,50,47,45,43,41,39,38,37,36,35,34,33,32,31,30,29,28,27,26,25,24,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1,0.99,0.98,0.97,0.96,0.95,0.94,0.93,0.92,0.91,0.9,0.89,0.88,0.87,0.86,0.85,0.84,0.83,0.82,0.81,0.8,0.79,0.78,0.77,0.76,0.75,0.74,0.73,0.72,0.71,0.7,0.69,0.68,0.67,0.66,0.65,0.64,0.63,0.62,0.1,0.6,0.59,0.58,0.57,0.56,0.55,0.54,0.53,0.52,0.51,0.5,0.49,0.48,0.47,0.46})

 

[jasonb75]

What you asked for is basically,

=LOOKUP(D5,A1:A100,B1:B100)

With the lookup and result vectors listed in columns A and B respectively, however, there may be a way to do it without listing everything out, but to find out if that is possible we would need a bit more information on the task you are trying to do.

Looking at the values in the result vector, {100,75,65,60,56....} I'm seeing no immediately obvious pattern, is there a known pattern, a formula to generate these values?

 

[fowler]

no these values are a predertimind score rating for irish dancing marking system i have been asked to drag in to the 21st century

 

[Aladin Akyurek]

no these values are a predertimind score rating for irish dancing marking system i have been asked to drag in to the 21st century

Enter 1, 2, 3, etc. one by one in column A and enter the corresponding 100, 75, 65, etc. in column B.

Now you can invoke:

=LOOKUP(D5,A:A,B:B)

 

[fowler]

thanks guys this has helped me 100%