Lookup of sorts...Matrix Array etc.
 

Hi Guy
B1:F1 are numbers 1-5. A1:A21 are numbers 100,200,300...2000. J1= a number 1-5, say 3 for example. J2 is a number 100-2000, say 300 for example. What formula would I use in J3 to return the value of D4 (4th column, 4th row)? Also, what type of validation would I use in J2 to only allow a value between 100 and 2000 multiples of 100
Thanks to all

Lookup of sorts...Matrix Array etc.
 

=vlookup(j2,A1:B21,2,False)

Use Data Validation and use a custom function

=And(J2=100,J2<=2000,Mod(J2,100)=0)

--
Regards,
Tom Ogilvy

"Michele" wrote in message
...
Hi Guys
B1:F1 are numbers 1-5. A1:A21 are numbers 100,200,300...2000. J1= a number
1-5, say 3 for example. J2 is a number 100-2000, say 300 for example. What
formula would I use in J3 to return the value of D4 (4th column, 4th row)?
Also, what type of validation would I use in J2 to only allow a value
between 100 and 2000 multiples of 100?
Thanks to all

Lookup of sorts...Matrix Array etc.
 

This works but doesnt take into account the J1 input, that would effevtively offset the look up by that value
Thanks!

Lookup of sorts...Matrix Array etc.
 

Hi Michele
not sure if you already got to the solution by yourself :-)
try
=vlookup(j2,A1:F21,1+j1,False)

Frank



Michele wrote:
This works but doesnt take into account the J1 input, that would
effevtively offset the look up by that value. Thanks!

Lookup of sorts...Matrix Array etc.
 

Thanks Franks! that works great. I cant get the CF to work however. This is what I am using for formula modified to fit my use. AND(X3=0,X3<=15000,MOD(X3,50)=0) Acceptable values are 0-15000 by 50'
Thanks again!

Lookup of sorts...Matrix Array etc.
 

Hi
do you want to use conditional format (CF) or a validation. Reading
your fist post I asumme the latter. To achieve this goto 'Data -
Validation' and enter the following formula:
=AND(X3=0,X3<=15000,MOD(X3,50)=0)
(dont forget the '=' sign)

Frank


Thanks Franks! that works great. I cant get the CF to work however.
This is what I am using for formula modified to fit my use.
AND(X3=0,X3<=15000,MOD(X3,50)=0) Acceptable values are 0-15000 by
50's Thanks again!