Forum Discussion
VLOOKUP show wrong values
Hi all, i have the following problem with an excel function.
I have a file with two sheets, sheet one (AD-Export) contains data for the employees (such as first name, email, department) and the second sheet (Members) contains a list of members (First name, email, department). I'm using the VLOOKUP formula every time when a new user is added in the Members sheet, to search by its email in the AD-Export one and fills out automatically the department.
The formula is like this: =VLOOKUP(C3;Table1;2;FALSE)
The problem is that the information in the table_array is correct, but the formula returns #N/A -
I don't know what I'm doing wrong, as it should be fairly simple formula.
thanks in advanced
Kiril
As HansVogelaar suggested
=IFNA( INDEX(Table1[Batch-ID], MATCH(Scouts!C2, Table1[mail], 0) ), "no such")
14 Replies
- I am having a similar issue. I am can't seem to post photos but I would if it was possible. I am trying to use the Vlookup for dates but when i do this I get a #Value error.
That's better to discuss with sample file. Perhaps you have texts which looks like dates, not actual dates which in Excel are numbers in behind.
The suggested formula work fine. Please see attached file.
Your VLOOKUP formula looks up C3, i.e. the email address, in the first column of Table1.
But the first column of Table1 that is visible in your screenshot is Division. Does Table1 begin in column A? If so, you should have included it in the screenshot.
Better attach a stripped-down copy of the workbook with some dummy data.
- I've missed to add the formula as well: =VLOOKUP(C2;'AD-Export-2021-10-14'!A2:J12;2;FALSE)
It's difficult to read the details in your attached files but i guess the formula should be like:
=VLOOKUP(C3;Table1!B2:C10;2;FALSE)
if the name of the table is Table1. If it's AD-Export then try
=VLOOKUP(C3;'AD-Export'!B2:C10;2;FALSE)
