As shown in the table above the Emp Id for Kevin is 303 and 423 for James, but after applying the LOOKUP formula it gives the same value for both of them, which is not right.

The formula is also mentioned in the attached Excel sheet...

Thank you for getting in touch with us. I've reviewed the worksheet you shared and noticed that you attempted to use the LOOKUP function, but the lookup_array wasn't sorted in ascending order. Please note that for the LOOKUP function to work properly, the lookup_array must be sorted in ascending order. To address this inconvenience, I suggest using an alternative function, namely the VLOOKUP function.

I have created an updated workbook where I've demonstrated the use of the VLOOKUP function in your case across all the worksheets. Please feel free to let us know if you encounter any issues. We're always happy to help.

but it's generating some kind of error #value! As highlighted (red color box) in the above image instead of giving NOT FOUND message, So what to do now

Formula used to perform this:- For more details kindly refer to this attachment:-
The formula is also mentioned in the attached Excel sheet...

View attachment 1047
but it's generating some kind of error #value! As highlighted (red color box) in the above image instead of giving NOT FOUND message, So what to do now

Formula used to perform this:- View attachment 1048 For more details kindly refer to this attachment:-
The formula is also mentioned in the attached Excel sheet...

Thanks for your well-layout explanation. On our end, we are getting the desired result. We are using Microsoft Excel 365.

Another suggestion: I have found the AT (@) sign after the Equal (=) sign in your formula, which means you are using a formula that is unavailable in the Excel Version. Maybe you should consider using VLOOKUP.

You can swap your Emp Id column. Keep your lookup array (Emp Id) on the left side. I've inserted an image and attached the Excel file for your better understanding.

Or you can use the VLOOKUP function with the IFERROR function. It will give the same result as the XLOOKUP function.