[Solved] Regarding Lookup formula

[ashish29]

I'm trying to find out the Emp Id using it's name as explained at :- https://www.exceldemy.com/types-of-lookup-in-excel/, but it is generating some wrong values... I'm using MS Office 2007 for this...

For Ex -:



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...

 

[Aniruddah Alam]

Hi Ashish,

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.

Best regards,
Aniruddah
Team Exceldemy

 

[ashish29]

Facing a problem with XLOOKUP:-

I'm trying to use this formula for XLOOKUP :-


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...

 

[Lutfor Rahman Shimanto]

Facing a problem with XLOOKUP:-

I'm trying to use this formula for XLOOKUP :-

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...

Hello Ashish29

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.

RESULT:

​

I hope this will resolve your problem. Good luck!

Regards
Lutfor Rahman Shimanto

 

[ashish29]

Hello Ashish29

Thanks for your well-layout explanation. I found a typo in the formula you are using. When typing the formula, you mistakenly write D5:54.

Another suggestion: If you ever find the AT (@) sign after the Equal (=) sign in your formula, remove that AT sign.

Corrected Formula:

=XLOOKUP(J10,E5:E54,D5:D54,"NOT FOUND")

RESULT:

View attachment 1049​

I hope this will resolve your problem. Good luck!

Regards
Lutfor Rahman Shimanto

Sir still it is giving same result #Value!

 

[shamimarita]

Sir still it is giving same result #Value!

Hello Ashish,

Hope you are doing well. Please tell me one thing, in which Excel version are you using the XLOOKUP function?

 

[ashish29]

Ok, i got it... XLOOKUP is only for Office 365 and MS Excel 2019...

 

[shamimarita]

Ok, i got it... XLOOKUP is only for Office 365 and MS Excel 2019...

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.



Kindly let us know if it worked or not for you.