Index match or vlookup suggestion with copy and paste automatically

Hi guys

I have clients in column E on my spreaddsheet which is updated daily.

In column H, I had dropdowns of categorys which I am now removing and replacing with a vlookup to prevent having to do the admin of using a dropdown whenever i have a client added in column e and needing to input the category in column H.

I have used a unique sort formula in column AA to capture the clients from column E and sort it A-Z. i USED a vlookup in column AB to capture the business unit for the clients from my list in column H, and then copied and pasted over the vlookup formula to turn these into values. My problem now becomes if i add a client in cell e32, my formula in cell h32 will return a random value from ab column due to the unique sort. So my question is, how or what should i do?

I am using this vlookup in column h: =IF(LEN(VLOOKUP(E2,AA$2:AB$100,2,FALSE))=0,"No BU listed",VLOOKUP(E2,AA$2:AB$100,2,FALSE))

Effectively my objective is anytime a new client is added in column E, I will have it build up in the list in column AA. My vlookup formula will look at column AB and say: no bu listed (meaning no category provided) and if the client already exists, the value from column AB will be returned

Any guidance much appreciated!

Hi
I didnt understand why do you change column AB to values.
while column AA is being sorted every time you add a new value the categories will no longer match the client list. This should be dynamic as well....

Don't sort

AG2
=UNIQUE(FILTER(E2:E3000,E2:E3000<>""))

AH2:AHxx input Business Unit

H2
=LET(v,VLOOKUP(E2,AG$2:AH$100,2,),IF(E2="","",IF(v="","No BU listed",v)))

yes I guess so Belinda.. I didn't quite have a plan and wanted suggestions on this too

thank you Bo_Ry this should do the trick!