lookup

fowler

New Member
Joined
Nov 2, 2014
Messages
5
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,221
Office Version
  1. 365
Platform
  1. Windows
Type =LOOKUP into any cell then press f1 to open the help file.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,221
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Nov 2, 2014
Messages
5
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

Well-known Member
Joined
Dec 30, 2008
Messages
13,221
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Nov 2, 2014
Messages
5
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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)
 

Forum statistics

Threads
1,136,968
Messages
5,678,866
Members
419,787
Latest member
juanam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top