I've just run into and solved a problem based on this difference. Basically I had an old line in my hosts file that meant I couldn't resolve two domains, so when I changed hosting for PiHole / Home Assistant from a server on one IP to a server on another IP it took me two days to work out what was going wrong.

I had my server on 192.168.1.42, but I turned that server off and moved it to 192.168.1.12. For some reason I had this entry in my hosts file

192.168.1.42 homeassistant.local

When I issued an nslookup it returned what I expected to see

nslookup homeassistant.local

> 192.168.1.12

Whereas when I issued a ping command I saw the truth

ping homeassistant.local

> 192.168.1.42

Ping returns the actual IP that Windows is going to send the request to, whereas nslookup looks up the domain from your DNS server. Therefore if you have defined anything in your hosts file nslookup can't see it, but ping can.

Two days it took me to work this out. Well, about 3 hours over two days, but still frustrating!