formula not working

[JayPee]

I'm not sure why this isn't working ... please help.

A B
0.999 1
0.799 2
0.699 3
0.599 4
0.499 5
0.399 6
0.299 7
0.199 8
0.99 9

I just need to lookup a value in ,say, C1 and compare to colA and return a
value in colB.
Using: =IF(M41=0,10,IF(M41>1,0,VLOOKUP(M41,$C$60:$D$68,2)))
yields #N/A for any value not 0 or >1.
What gives?

 

[Niek Otten]

You did not include a 4th argument in your VLOOKUP call. That means that the table has to be sorted ascending. Bit it isn't.
Sort your table or use FALSE as 4th argument (which is probably what you require).
Make sure the underlying values match exactly, not just the values shown thru formatting.


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I'm not sure why this isn't working ... please help.
|
| A B
| 0.999 1
| 0.799 2
| 0.699 3
| 0.599 4
| 0.499 5
| 0.399 6
| 0.299 7
| 0.199 8
| 0.99 9
|
| I just need to lookup a value in ,say, C1 and compare to colA and return a
| value in colB.
| Using: =IF(M41=0,10,IF(M41>1,0,VLOOKUP(M41,$C$60:$D$68,2)))
| yields #N/A for any value not 0 or >1.
| What gives?
|
|
|

 

[JayPee]

Thank you ... sorting colA in ascending order worked!