VLOOKUP isn't working for LOTS of data

Question

I have a workbook with 4 sheets (Search, Active1, Active2 and Inactive).&nbsp; Search is where the user will enter an NPI# to search the other 3 worksheets for EVERY appearance of that NPI#, presenting data from selected columns of the row that NPI# is on.&nbsp; On average there are 40,000 rows and 16 columns of data.
&nbsp;
I have been using a VLOOKUP with limited success.&nbsp;I have a feeling that VLOOKUP&nbsp;isn't the right formula for my needs.&nbsp;I need help figuring out how to make SOMETHING work on this database.&nbsp; I have attached a smaller version (only 10 rows and 16 columns)&nbsp;as an example of the formulas and layout I'm working with.
&nbsp;
I would certainly appreciate any help.

detlef_lewin · Answer

"When I use the same formulas (modified to include 40,000 rows instead of 10) and add in the&nbsp;real&nbsp;data&nbsp;it returns '#N/A'. Excel gives no error messages."
&nbsp;
I'm confused. What is #N/A if not an error message?
&nbsp;
#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.
&nbsp;

null null · Answer

That was it!!!!!&nbsp; It wasn't capitalization, but something similar...I was searching with a NUMBER.&nbsp; The fields in the worksheets where designated as&nbsp;'TEXT'' or 'GENERAL'.&nbsp; Once I reformatted the&nbsp;cells as numbers, it returned values as hoped.
&nbsp;
THANK YOU! THANK YOU!&nbsp;You were invaluable.
~Alicia&nbsp;

detlef_lewin · Answer

Hi,
&nbsp;
there is no attachment.
&nbsp;

null null · Answer

Sorry...left off the attachment

detlef_lewin · Answer

VLOOKUP() works perfectly. But you should change the sheet name from&nbsp;Acitve1 to&nbsp;Active1 to reflect the sheet name used in C2, D2 and F2.
And E2 and E3 don't have formulas at all.
&nbsp;

Forum Discussion

VLOOKUP isn't working for LOTS of data

8 Replies