Help with either VLOOKUP or IF AND statements

[Ken Richins]

I use this to find a date in one of twelve sheets in my workbook. I need to modify it so that it will find both sheets that the value could be in. the twelve sheets are query tables that divide the master table into the annual and semi-annual months for each employee. that means each employee is found on two sheets. I need to find the appointment date for both annual and or semi-annual sheet and give me the newest date for the master sheet. Here is what I have that works great to find the first but does not work for the semi dates.
{=VLOOKUP(A4,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$2:$M$600"),A4)>0),0))&"'!$A$1:$M$600"),13,FALSE)}

[#2]

Hi

Why don't you query the master table? Or even better: Why don't you filter the master table?

[#3]

cause the master is not used as an input sheet only as a consolidator sheet for reports. the 12 monthly tabs are the data input sheets hence the need to gather the data from the 12 input sheets. I can't use vb or macros as the sheets are edited using excel online using SharePoint.

[#4]

*

---

@Ken Richins wrote:

cause the master is not used as an input sheet only as a consolidator sheet for reports. the 12 monthly tabs are the data input sheets hence the need to gather the data from the 12 input sheets.

---

But you stated that "the twelve sheets are query tables that divide the master table into the annual and semi-annual months".

Either the twelve tables are input or queries. They can't be both.

It would be better if supply a sample workbook with the desired result.

---

@Ken Richins wrote:

I can't use vb or macros as the sheets are edited using excel online using SharePoint.

---

Macros can be executed if you use Excel Desktop.

[#5]

for this application we can NOT use MACROS as all input must be done using SharePoint