02-06-2018 09:57 AM
02-06-2018 09:57 AM
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.
02-06-2018 11:39 AM
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.
02-06-2018 11:45 AM
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.
02-07-2018 01:19 PM
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)?
02-07-2018 02:09 PM
"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.
02-07-2018 02:35 PM
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.