You can think of the fork as a filter with one of its functions to dampen unwanted vibrations from the road. When you tap the fork and listen to it you're measuring the resonant frequency of the fork. Vibrations at the resonant frequency will receive little dampening from the fork and be transmitted to your hands. I don't know what vibrations from the road look like but I believe fork design is a compromise between transmitting enough feedback to provide crisp handling vs dampening out unwanted vibrations.

I suspect Carbon transmits a broader spectrum of vibration which allows for crisper handling. Steel will dampen more road noise but also will be less responsive to steering inputs.

Personally, I prefer a stiffer fork which will allow crisp handling when you need it and use different tires to handle rougher roads if necessary. With a noodly fork there is nothing you can do to improve handling.