Forum Discussion
Roymore
Jan 18, 2024Copper Contributor
Attempting to use vlookup to auto populate sheet and returning #N/A on some cells
The formula I have is =VLOOKUP(TRIM($F2), Serial!$A$2:$C$997, 3, FALSE), so matching a serial number of up to 10-12 numbers, in a serial number database to return customer name and job type. It w...
- Jan 18, 2024
Thanks. Although the relevant columns are both formatted as Text, Excel sees some of the values as text and others as numbers (you can check this using the ISTEXT function).
If you have Microsoft 365 or Office 2021, you can use this formula in B2:
=XLOOKUP($D2&"", Serial!$A$2:$A$1000&"", Serial!B$2:B$1000, "")
Fill to the right to C2, then down to the last used row.
If you have an older version:
=IFERROR(INDEX(Serial!B$2:B$1000, MATCH($D2&"", Serial!$A$2:$A$1000&"", 0)), "")
I'll send you the workbook with both approaches by PM.
Roymore
Jan 18, 2024Copper Contributor
Thanks Hans.
I have sent you a PM
I have sent you a PM
HansVogelaar
Jan 18, 2024MVP
Thanks. Although the relevant columns are both formatted as Text, Excel sees some of the values as text and others as numbers (you can check this using the ISTEXT function).
If you have Microsoft 365 or Office 2021, you can use this formula in B2:
=XLOOKUP($D2&"", Serial!$A$2:$A$1000&"", Serial!B$2:B$1000, "")
Fill to the right to C2, then down to the last used row.
If you have an older version:
=IFERROR(INDEX(Serial!B$2:B$1000, MATCH($D2&"", Serial!$A$2:$A$1000&"", 0)), "")
I'll send you the workbook with both approaches by PM.