• 469K Members
• 4,626 Online
• 568K Conversations

New Contributor

# Help with either VLOOKUP or IF AND statements

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

# Re: Help with either VLOOKUP or IF AND statements

Hi

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

# Re: Help with either VLOOKUP or IF AND statements

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.

# Re: Help with either VLOOKUP or IF AND statements

*

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

# Re: Help with either VLOOKUP or IF AND statements

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
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies