[Solved] Regarding Lookup formula

ashish29

New member
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 -:

1700110648127.jpeg

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.

1700111280165.png

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

Attachments

  • Lookup in Excel.xlsx
    16.8 KB · Views: 5
Last edited:
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.
1700126024723.png

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
 

Attachments

  • Lookup in Excel_Updated.xlsx
    20 KB · Views: 2
Facing a problem with XLOOKUP:-

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

1700471164018.jpeg
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:-
1700471400064.png
For more details kindly refer to this attachment:-
The formula is also mentioned in the attached Excel sheet...
 

Attachments

  • XLookup in Excel.xlsx
    10.3 KB · Views: 7
Last edited:
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:
Output of XLOOKUP formula (Ashish29).png

I hope this will resolve your problem. Good luck!

Regards
Lutfor Rahman Shimanto
 
Last edited:
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:
Code:
=XLOOKUP(J10,E5:E54,D5:D54,"NOT FOUND")

RESULT:

I hope this will resolve your problem. Good luck!

Regards
Lutfor Rahman Shimanto
Sir still it is giving same result #Value!
 
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.


XLookup in Excel.png

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

vLookup in Excel.png

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

Attachments

  • XLookup in Excel.xlsx
    14.5 KB · Views: 2
Last edited:

Online statistics

Members online
0
Guests online
7
Total visitors
7

Forum statistics

Threads
311
Messages
1,378
Members
568
Latest member
WilliamHon
Top