Lookup of sorts...Matrix Array etc.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=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 said:
Hi Guys
B1:F1 are numbers 1-5. A1:A21 are numbers 100,200,300...2000. J1= a number
Click to expand...
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?
 
This works but doesnt take into account the J1 input, that would effevtively offset the look up by that value
Thanks!
 
Hi Michele
not sure if you already got to the solution by yourself :-)
try
=vlookup(j2,A1:F21,1+j1,False)

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'
Thanks again!
 
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
 
Back
Top