Search List in a Column Return a Result From Another Column

Ok

I had a working VBA for what I wanted to do here, unfortunately I have to upload this document to googlesheets. I am hoping I can do what I want in functions instead and then convert to a googlesheet.

I have attached the file. In the sheet titles "clans" there is a list of allies and enemy. The intention is to have a lookup sheet so a user will enter a clan name into D6 on search sheet and the result will return in D10.

As you can see I can find a clan using VLook, but I need more. These things are:

1. Return Allies or Enemy (shown in column B in clans sheet), green background for allies and red background for enemy.
2. If nothing found, a not found response.
3. Only giving access to D6 to the user and hiding clans sheet. (When I hide it doesn't find).

Thanks

To get "Allies/Enemy"

==IFERROR(VLOOKUP(D6,clans!A1:B5000,2,FALSE),"Not found")

If sheet is hidden, VLOOKUP still works. (in Excel)