Forum Discussion
VLOOKUP isn't working for LOTS of data
Thank you for looking at the formulas. I can see how I wrote those two incorrectly.
You are correct! There is no data in those two cells because there is no data to retrieve and will always be #N/A.
Where I continue to run into problems is when I use these exact formulas on all 120,000 rows (across the 3 spreadsheets). Any thoughts on this?
I really appreciate your discerning eye and feedback.
What exactly are your problems?
I really appreciate your repeated replies. They have been helpful for sure!
The formulas work GREAT in the sample workbook you've been viewing.
When I use the same formulas (modified to include 40,000 rows instead of 10) and add in the real data it returns '#N/A'. Excel gives no error messages.
I have reviewed the formulas and they are accurate. Out of desperation, I rewrote the formulas using the wizard and selected (rather than typing in) the variables. Excel gives no errors or other clues why it doesn't work.
I'm stumped. Sadly I cannot send the full workbook for you to review. The workbook contains federally protected health information.
Any other thoughts or search mechanisms I can use (outside of CTRL-F)?
Regards,
Alicia
"When I use the same formulas (modified to include 40,000 rows instead of 10) and add in the real data it returns '#N/A'. Excel gives no error messages."
I'm confused. What is #N/A if not an error message?
#N/A simply states that there is no match for the search criteria. Either the search criteria is not in the search vector or there are little differences which might be difficult to recognize:
l (small L) vs. I (big i) vs. |
Or additional spaces which can be discovered by comparing the length of the text.
That was it!!!!! It wasn't capitalization, but something similar...I was searching with a NUMBER. The fields in the worksheets where designated as 'TEXT'' or 'GENERAL'. Once I reformatted the cells as numbers, it returned values as hoped.
THANK YOU! THANK YOU! You were invaluable.
~Alicia