Home

Help with either VLOOKUP or IF AND statements

Ken Richins
New Contributor

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)}

4 Replies

Hi

 

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

 

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.

*


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

 

for this application we can NOT use MACROS as all input must be done using SharePoint
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies