Home

VLOOKUP isn't working for LOTS of data

Highlighted
null null
Occasional Contributor

VLOOKUP isn't working for LOTS of data

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.

8 Replies

Re: VLOOKUP isn't working for LOTS of data

Hi,

 

there is no attachment.

 

Re: VLOOKUP isn't working for LOTS of data

Sorry...left off the attachment

Re: 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.

 

Re: 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.

Re: VLOOKUP isn't working for LOTS of data

What exactly are your problems?

 

Re: VLOOKUP isn't working for LOTS of data

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

Re: VLOOKUP isn't working for LOTS of data

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

 

Re: VLOOKUP isn't working for LOTS of data

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 

Related Conversations
Need some help with my Vlookup or a new idee
Jim Brastad in Excel on
5 Replies
VLOOKUP
Andrew Mesplay in Excel on
3 Replies
TRUE vlookup brings the wrong results
Sotiris Moschou in Excel on
2 Replies
NEW POWER QUERY - VLOOKUP WON'T LOOKUP
Jacob Croxton in Excel on
0 Replies