Vlook up function causing file open prompt

nikkil85 Are 'Pivot Table A' and 'Pivot Table B' the names of your pivot tables, or the names of the worksheets where the pivot tables are located? Unlike an Excel table, a pivot table cannot be referenced in a formula by name. In your first VLOOKUP formula, for example, 'Pivot Table A'!$A$2:$B$100 is referring to range A2:B100 on worksheet 'Pivot Table A'. If no such worksheet name exists in the workbook, Excel assumes the range reference is external, and will prompt you to open the source file where this range can be found. Be sure to use the Sheet Name in your formula where the data is located.

If you're still experiencing issues, please consider sharing a mock-up file, or screenshots showing your current setup (including the sheet tabs) with sensitive data blacked-out. There may be alternative methods to VLOOKUP that are more appropriate here, but additional information is required in order to make such suggestions.