Forum Discussion
VLOOKUP isn't working for LOTS of data
VLOOKUP() works perfectly. But you should change the sheet name from Acitve1 to Active1 to reflect the sheet name used in C2, D2 and F2.
And E2 and E3 don't have formulas at all.
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.