(Quick response) - you got lucky (I'm not part of Companies House). Caveat - I haven't used http in Excel or tested any of the below.
Questions about how to send http requests to which return data to Excel might be better addressed on an Excel forum. There's no harm in you asking in case someone else has done it here but your request does sound a bit like "do my homework for me". Did you search the forum ("magnifying glass" icon, top right)?
Searching here just now I found a thread in which someone's (incidentally) documented what they were doing to connect to Companies House from Excel - maybe you could start there?
Someone's already written code to automate some things in Excel and made it available:
Two quick points:
- If you've got a lot of data, don't forget about the rate-limiting in the API - if you exceed this CH may block your access.
- There may be a simpler solution by exporting your existing data from Excel (e.g. to CSV) and getting another program to use this to do the Companies House lookup and slot the data you need back in to your file.
A quick Google just found help with http / Excel: