Related Posts
Hello Wipro
I joined Wipro on 14th April.
I can be a part of any permotion or salary hike.
For new announcement or anything
Query 2 what is the notice period in probation.
Question 3 probation is for how many months.
Thanks
Wipro Tata Consultancy Capgemini Infosys Accenture Samsung Tech Mahindra IBM
Any other recruiters in this bowl?
Additional Posts in Excel Genius
Visual Storyteller
Would anyone be willing to teach me vlookup?
Coach
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
It sounds like you've taken many of the right steps to troubleshoot your VLOOKUP function. Here are a few additional things to check that might help resolve the issue:Ensure Exact Match:Make sure your VLOOKUP function is set to look for an exact match by using FALSE as the last argument:=VLOOKUP(A1, 'Workbook2.xlsx'!Sheet1!$A$1:$B$100, 2, FALSE)
Check for Leading/Trailing Spaces:Sometimes invisible characters or extra spaces can cause issues. Even though you used text-to-columns, you might still want to try TRIM to ensure there are no leading or trailing spaces:=VLOOKUP(TRIM(A1), 'Workbook2.xlsx'!Sheet1!$A$1:$B$100, 2, FALSE)
Check Data Types:Ensure the data types are consistent. Even though you've set both columns to "General," Excel sometimes treats numbers stored as text differently from actual numbers. Try converting all serial numbers to text explicitly:=VLOOKUP(TEXT(A1, "0"), 'Workbook2.xlsx'!Sheet1!$A$1:$B$100, 2, FALSE)
Named Ranges:If you're referencing another workbook, using named ranges can sometimes help avoid reference issues. Define a named range in the source workbook and use it in your VLOOKUP.Workbook References:Ensure the workbook you're referencing is open. Sometimes VLOOKUP may not work properly if the referenced workbook is closed.Check for Non-printing Characters:Use the CLEAN function to remove any non-printing characters that might be causing the mismatch:=VLOOKUP(CLEAN(A1), 'Workbook2.xlsx'!Sheet1!$A$1:$B$100, 2, FALSE)
Double-check the Range:Verify the range in the VLOOKUP function is correct and includes the column with the serial numbers and the column with the return values.Array Formulas:If the VLOOKUP is part of an array formula, ensure you press Ctrl+Shift+Enter instead of just Enter.If none of these solutions work, it might be helpful to break down the problem further. Create a new column in both workbooks that simply references the serial numbers and compare these new columns to see if there’s still an issue. This might help isolate where the problem lies.
ChatGPT is your best friend for these questions
Incredible *
Did you sort correctly?
You’ve either got to sort the column or just use another function like XLOOKUP or index(match())
Make sure ur text type are the same. Make them both general
Xlookup and index match are better to use
Learn Index/Match. So much better, stronger, more versatile than VLookup.
Uhh no lol
If you are still trying to solve this, are you able to post a screenshot of one of the N/As so we can see the data? Also if you can copy and paste the exact cell data for the criteria and the same from the column you expect it to find this match in before it returns the offset data that should provide answers...
Mentor
Did you set the last argument to be true?
Could you provide a screenshot?
Go to file— options— formula— and then see if the formula setting is set to Automatic or manual. It should be automatic. See if it works and let me know
Try copying and pasting a value from your source into the lookup table, converting to CSV and then bank to XLS or just creating a new file for each by copying into a txt file then back into excel.
Yeah need more info, can you confirm the full formula, if the lookup value is correct, is your formula looking at the right array and column?
If not then easiest thing to do is Data > Evaluate Formula and run through the steps to see where the formula breaks. When you see the step that causes the N/A, fix it.
This is guaranteed to show you why the formula breaks.
Good suggestions in this thread. I will add to use control+~ (tilde) to check your formulas when you are applying the vlookup.