View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Bernd Bernd is offline
external usenet poster
 
Posts: 92
Default need a macro to sort and replace data

Hello again,

If your input values are in columns A:P, then enter into ...
Q1:
=LOOKUP(A1,{0,3.001,7.001},{1,2,3})
R1:
=LOOKUP(B1,{0,5.001,8.001},{1,2,3})
and copy down as far as necessary, for example.

Have a look into Excel's help on LOOKUP.

A more precise approach could even be in Q1:
=LOOKUP(-A1,{-1E+304,-7,-3},{3,2,1})

[Imagine what happens if your input would be 7.0001 in cell A1. Test
it with both formulas.]

Regards,
Bernd