Return value from range with "range" of values
 

Would like to have lookup for a cell value but table consists of low and high
values with third column the value to be returned.

For example, lReturn value from F2:F12, based on entry in B2 by looking to
values in D2:E12. Below would return $850.

Could use "nested ifs", but Fair Market Value table is considerably more
than 10 rows of values.


A B C D E F
1 Fair Market Value ALV
2 Query 1250 $1 $999 $600
3 ALV $1,000 $1,999 $850
4 $2,000 $2,999 $1,100
5 $3,000 $3,999 $1,350
5 $4,000 $4,999 $1,600
6 $5,000 $5,999 $1,850
7 $6,000 $6,999 $2,100
8 $7,000 $7,999 $2,350
10 $8,000 $8,999 $2,600
11 $9,000 $9,999 $2,850
12 $10,000 $10,999 $3,100


Thanks for your help and guidance.
Kindly,
- mary ann

Return value from range with "range" of values
 

Try this:

=VLOOKUP(B2,D2:F12,3)

--
Biff
Microsoft Excel MVP


"M. A. Halbin" wrote in message
...
Would like to have lookup for a cell value but table consists of low and
high
values with third column the value to be returned.

For example, lReturn value from F2:F12, based on entry in B2 by looking
to
values in D2:E12. Below would return $850.

Could use "nested ifs", but Fair Market Value table is considerably more
than 10 rows of values.


A B C D E F
1 Fair Market Value ALV
2 Query 1250 $1 $999 $600
3 ALV $1,000 $1,999 $850
4 $2,000 $2,999 $1,100
5 $3,000 $3,999 $1,350
5 $4,000 $4,999 $1,600
6 $5,000 $5,999 $1,850
7 $6,000 $6,999 $2,100
8 $7,000 $7,999 $2,350
10 $8,000 $8,999 $2,600
11 $9,000 $9,999 $2,850
12 $10,000 $10,999 $3,100


Thanks for your help and guidance.
Kindly,
- mary ann

Return value from range with "range" of values
 

=VLOOKUP(B2,D2:F11,3)


"M. A. Halbin" wrote:

Would like to have lookup for a cell value but table consists of low and high
values with third column the value to be returned.

For example, lReturn value from F2:F12, based on entry in B2 by looking to
values in D2:E12. Below would return $850.

Could use "nested ifs", but Fair Market Value table is considerably more
than 10 rows of values.


A B C D E F
1 Fair Market Value ALV
2 Query 1250 $1 $999 $600
3 ALV $1,000 $1,999 $850
4 $2,000 $2,999 $1,100
5 $3,000 $3,999 $1,350
5 $4,000 $4,999 $1,600
6 $5,000 $5,999 $1,850
7 $6,000 $6,999 $2,100
8 $7,000 $7,999 $2,350
10 $8,000 $8,999 $2,600
11 $9,000 $9,999 $2,850
12 $10,000 $10,999 $3,100


Thanks for your help and guidance.
Kindly,
- mary ann