Looking up data from mixed format list

I am trying to do a formula to look up the zip code on the "Tanika All" sheet against the list of zip codes in each the "Tanika Medical" & "Tanika Dental" worksheet. At first I wanted it to return the text "Medical" if it was found in the medical sheet or "dental" if it was found on the dental sheet. Or "Both" if it was found on both sheets.

There is mixed formatting and it's a huge list of data. So I have tried vlookups & index match, if functions, sumproduct functions trying to get even just two lists that lookup if the zip code matches to the other two spreadsheets. I used the formula below and made it an array formula and was able to get it for the most part to match everything but the ones that start with 0's.

Someone please help me.

=IFERROR(VLOOKUP(""&F2&"",TEXT('[Tanika Medical.xlsx]Sheet1'!$A:$A,"0"),1,FALSE),"")


*****I had to remove half of the lines on the medical file to get it to upload to this post. There is usually almost 47,000 lines*****

Is there any reason why you have the Dental and Medical tables as separate files, rather than separate sheets in one main workbook?

It would help if you had the same format for both these files - in Dental you have fields for State, City and Zip, and in Medical you have Zip, State, County and City. In your All file you have City State and Zip, so it would be better if they all followed the same layout.

Hope this helps.

Pete

Originally Posted by Pete_UK

Is there any reason why you have the Dental and Medical tables as separate files, rather than separate sheets in one main workbook?

It would help if you had the same format for both these files - in Dental you have fields for State, City and Zip, and in Medical you have Zip, State, County and City. In your All file you have City State and Zip, so it would be better if they all followed the same layout.

Hope this helps.

Pete

These reports are generated by the system and this is exactly how they come out. This is the raw data. The medical and dental sheets are not in the same workbook because once the lines on the All workbook that have the zip codes that show up on either medical or dental or both the medical and dental workbooks are no longer needed. They are simply for reference and the all workbook is used in reporting. I have tried moving around the columns manually and it didn't make a difference I still can't get it to work properly.

Okay then, open all 3 files in the same instance of Excel, and then in D2 of the Tanika All file you can have this formula:

=IF(ISNA(MATCH(C2,'[Tanika Medical.xlsx]Sheet1'!$A:$A,0)),"","Yes")

Copy this down to the bottom of your data by double-clicking the fill handle on D2 (the small black square in the bottom right corner of the cursor). Then in E2 you can have this formula:

=IF(ISNA(MATCH(C2*1,'[Tanika Dental.xlsx]Sheet1'!$C:$C,0)),"","Yes")

and copy this down in the same way.

Then you can close the Medical and Dental files, and Excel will automatically expand the file references to include the full path to those files, and in future you will not need to open the files.

Hope this helps.

Pete