Love the image, but next post - please use the ALT+X Go Adavanced feature to share a workbook.

I enjoy helping others, but if I have to retype your image it becomes a nuisance and will discourage others from assisting.

That being said, a lookup formula is one I basically consider dead in the library of formulas.

Move to a Vlookup or Index/Match.

You will find much more success in utilizing these.

Attempt them in your book and then share your test (Make a test sheet with fake names instead of coloring over names and colours)

Then share the test file so we can further assist you!