Oct 25 2021 08:33 AM
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
Oct 25 2021 08:41 AM
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)
Oct 25 2021 08:46 AM
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.
Oct 25 2021 08:59 AM
Oct 25 2021 09:03 AM
Oct 25 2021 09:14 AM - edited Oct 25 2021 09:25 AM
=VLOOKUP(C2,CHOOSE({1,2},'AD-Export-2021-10-14'!$J$2:$J$14,'AD-Export-2021-10-14'!$B$2:$B$14),2,FALSE)
You want vlookup to the left. Please enter this formula as arrayformula with ctrl+shift+enter.
Oct 25 2021 09:15 AM
VLOOKUP always searches for the lookup value in the first column of the range.
Use
=IFERROR(INDEX('AD-Export-2021-10-14'!$B$2:$B$12, MATCH(C2, 'AD-Export-2021-10-14'!$J$2:$J$12, 0)), "")
Oct 25 2021 09:25 AM
@Hans Vogelaar I'm uploading a copy of the file with some sample data
Neither of the proposed formulas worked, unfortunately i don't want to rearrange the columns in AD Export sheet as there are other sheets from the file that are referring to this sheet and they have formulas that might break.
regards
Oct 25 2021 09:32 AM
SolutionAs @Hans Vogelaar suggested
=IFNA( INDEX(Table1[Batch-ID], MATCH(Scouts!C2, Table1[mail], 0) ), "no such")
Oct 25 2021 09:38 AM
The suggested formula work fine. Please see attached file.
Oct 25 2021 09:40 AM
Oct 25 2021 09:54 AM
That was one of few reasons why XLOOKUP() was introduced - VLOOKUP() works only to the right and use fixed returned column number; INDEX/MATCH looks bit complex for many users.
Nov 03 2021 11:36 AM
Nov 03 2021 12:06 PM
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.
Oct 25 2021 09:32 AM
SolutionAs @Hans Vogelaar suggested
=IFNA( INDEX(Table1[Batch-ID], MATCH(Scouts!C2, Table1[mail], 0) ), "no such")