Thanks for any help.
I would like to look up an array like this:
A1-D1:Def 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,A2:D5,MATCH(year,A1:D1)) '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 can't picture your layout
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ian Elliott" wrote in message
...
Thanks for any help.
I would like to look up an array like this:
A1-D1:Def 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,A2:D5,MATCH(year,A1:D1)) '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.

=VLOOKUP(A6,$A$2:$D$5,MATCH(B6,$1:$1))


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
I can't picture your layout
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ian Elliott" wrote in message
...
Thanks for any help.
I would like to look up an array like this:
A1-D1:Def 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,A2:D5,MATCH(year,A1:D1)) '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.