Find Last Value using LOOKUP Function
This video shows how to use the LOOKUP value to fix it when it is giving incorrect value. LOOKUP can search the last entry instead of first one unlike VLOOKUP or INDEX MATCH that will always give you the first lookup value found.
As a bonus topic, this video also shows how to find the last lookup value using XLOOKUP.