lookup formulas
 

I dont think I expressed myself clearly.

The initial lookup formula (given below) works well and returns a value in M7.

=LOOKUP(L7,{0,15,15.1,15.4,15.5,15.9,16,16.9,17,17 .9,18,18.9,19,19.9,20,20.9,21,21.9,22,22.9,23,23.9 ,24,24.9,25.0259},{"1.00","1.00","2.50","2.50","4. 00","4.00","5.50","5.50","6.50","6.50","7.00","7.0 0","8.75","8.75","9.50","9.50","10.75","10.75","12 .00","12.00","13.25","13.25","14.50""14.50","15.75 ","15.75"})

The value of M7 could be any of the following

1.00
2.50
4.00
5.50
6.50
7.00
8.75
9.50
10.75
12.00
13.25
14.50
15.75

In O7, I need to look at the value of M7 (being drawn from the lookup
formula looking at the value in L7), and depending which value was returned,
return yet another value eg

1.00 returns £0.00
2.50 returns £1.75
4.00 returns £3.50
5.50 returns £4.50
6.50 returns £5.25
And so on to
15.75 returns £16.00

I have tried to do this by using a similar lookup formula to the first,
however it returns #NA! I dont know if this is because I am doing something
wrong or because it is not possible to perform a lookup formula upon a
previous lookup formula.

lookup formulas
 

What was wrong the answer you got earlier today?

"Skibee" wrote:

I dont think I expressed myself clearly.

The initial lookup formula (given below) works well and returns a value in M7.

=LOOKUP(L7,{0,15,15.1,15.4,15.5,15.9,16,16.9,17,17 .9,18,18.9,19,19.9,20,20.9,21,21.9,22,22.9,23,23.9 ,24,24.9,25.0259},{"1.00","1.00","2.50","2.50","4. 00","4.00","5.50","5.50","6.50","6.50","7.00","7.0 0","8.75","8.75","9.50","9.50","10.75","10.75","12 .00","12.00","13.25","13.25","14.50""14.50","15.75 ","15.75"})

The value of M7 could be any of the following

1.00
2.50
4.00
5.50
6.50
7.00
8.75
9.50
10.75
12.00
13.25
14.50
15.75

In O7, I need to look at the value of M7 (being drawn from the lookup
formula looking at the value in L7), and depending which value was returned,
return yet another value eg

1.00 returns £0.00
2.50 returns £1.75
4.00 returns £3.50
5.50 returns £4.50
6.50 returns £5.25
And so on to
15.75 returns £16.00

I have tried to do this by using a similar lookup formula to the first,
however it returns #NA! I dont know if this is because I am doing something
wrong or because it is not possible to perform a lookup formula upon a
previous lookup formula.

lookup formulas
 

What was wrong the answer you got earlier today?

Or the one you got several days ago in a different forum?

Your best approach is to create a *TABLE*. Get rid of that long formula!

Why are the values duplicated in the result_vector?

{"1.00","1.00","2.50","2.50","4.00","4.00"...}

Why do you have those numners enclosed in quotes? That makes them TEXT
numbers, not NUMERIC numbers.

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
What was wrong the answer you got earlier today?

"Skibee" wrote:

I don't think I expressed myself clearly.

The initial lookup formula (given below) works well and returns a value
in M7.

=LOOKUP(L7,{0,15,15.1,15.4,15.5,15.9,16,16.9,17,17 .9,18,18.9,19,19.9,20,20.9,21,21.9,22,22.9,23,23.9 ,24,24.9,25.0259},{"1.00","1.00","2.50","2.50","4. 00","4.00","5.50","5.50","6.50","6.50","7.00","7.0 0","8.75","8.75","9.50","9.50","10.75","10.75","12 .00","12.00","13.25","13.25","14.50""14.50","15.75 ","15.75"})

The value of M7 could be any of the following

1.00
2.50
4.00
5.50
6.50
7.00
8.75
9.50
10.75
12.00
13.25
14.50
15.75

In O7, I need to look at the value of M7 (being drawn from the lookup
formula looking at the value in L7), and depending which value was
returned,
return yet another value eg

1.00 returns £0.00
2.50 returns £1.75
4.00 returns £3.50
5.50 returns £4.50
6.50 returns £5.25
And so on to
15.75 returns £16.00

I have tried to do this by using a similar lookup formula to the first,
however it returns #NA! I don't know if this is because I am doing
something
wrong or because it is not possible to perform a lookup formula upon a
previous lookup formula.