I
Ian Elliott
Thanks for any help.
I would like to look up an array like this:
A1-D1ef Per x yrs,2008,2009,2010
A2-A5:0<x<=5,5<x<=10,10<x<=20,20<x<=120 (these could just be labels, with
numbers like 5, 10, 20 & 120 to a column to the right for the lookup, if
necessary, but if the formula could use these for lookups, that's fine too)
B2-D5:the look up values, let's say: 1,2,3,4,5,6,7,8,9,10,11,12 (by row then
by col. So C3 is 6).
I would like to look up for 2008 & 0<x<=5 (so x might be 1 or 2.5 for
example) is 1
2008, 5<x<=10 is 2
2008,10<x<=20 is 3
2008,20<x<=120 is 4
2009,0<x<=5 is 5
2009,5<x<=10 is 6
etc
I would like to do this with a nested look-up/search/find statement, rather
than a bunch of ifs, or even one if.
I tried:
=VLOOKUP(x,A25,MATCH(year,A11)) 'and remember, the A2-A5 is not
'0<x<=5, etc, but 5 10 20 120.
but I couldn't get this to work.
Any ideas?
thanks again.
I would like to look up an array like this:
A1-D1ef Per x yrs,2008,2009,2010
A2-A5:0<x<=5,5<x<=10,10<x<=20,20<x<=120 (these could just be labels, with
numbers like 5, 10, 20 & 120 to a column to the right for the lookup, if
necessary, but if the formula could use these for lookups, that's fine too)
B2-D5:the look up values, let's say: 1,2,3,4,5,6,7,8,9,10,11,12 (by row then
by col. So C3 is 6).
I would like to look up for 2008 & 0<x<=5 (so x might be 1 or 2.5 for
example) is 1
2008, 5<x<=10 is 2
2008,10<x<=20 is 3
2008,20<x<=120 is 4
2009,0<x<=5 is 5
2009,5<x<=10 is 6
etc
I would like to do this with a nested look-up/search/find statement, rather
than a bunch of ifs, or even one if.
I tried:
=VLOOKUP(x,A25,MATCH(year,A11)) 'and remember, the A2-A5 is not
'0<x<=5, etc, but 5 10 20 120.
but I couldn't get this to work.
Any ideas?
thanks again.