VLOOKUP isn't working for LOTS of data

[null null]

I have a workbook with 4 sheets (Search, Active1, Active2 and Inactive).  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.  On average there are 40,000 rows and 16 columns of data.

I have been using a VLOOKUP with limited success. I have a feeling that VLOOKUP isn't the right formula for my needs. I need help figuring out how to make SOMETHING work on this database.  I have attached a smaller version (only 10 rows and 16 columns) as an example of the formulas and layout I'm working with.

I would certainly appreciate any help.

[#2]

Hi,

there is no attachment.

[#3]

Sorry...left off the attachment

[#4]

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.

[#5]

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.

[#6]

What exactly are your problems?

[#7]

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

[#8]

"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.

[#9]

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