Return a date from another sheet based on two columns in source sheet

I would like to be able to return a date in column "Due Date" based on conditions in columns A, B, and C. The dates will be pulled from a second sheet.

If Sheet 1 column B is "PU," I want it to search Sheet 2 for the same entry in Column A as Column A in Sheet 1 and return the result in the 2nd column.

If Sheet 1 column B is "New" and column C is "photo," I want it to search Sheet 2 for the same entry in Column A as Column A in Sheet 1 and return the result in the 3rd column.

If Sheet 1 column B is "New" and column C is "art," I want it to search Sheet 2 for the same entry in Column A as Column A in Sheet 1 and return the result in the 4th column.

Would this be an INDEXCOLLECT or a VLOOKUP? The structure of the ifs also has me a little confused. Is this possible to pull off?

Find more posts tagged with

Formulas

Accepted answers

amarkumar

@MRL Yes - this can be done, and VLOOKUP is the cleanest here as long as Sheet 2’s Column A is the lookup key and the return dates are in Columns B, C, and D.

Assumptions :

Sheet 1 has: Column A = key, Column B = status, Column C = type, Due Date = result
Sheet 2 has: Column A = same key, Column B = PU date, Column C = photo date, Column D = art date
Keys in Sheet 2 Column A are unique (or if you are Ok with the “first match wins”)

Create one cross-sheet range

Create a cross-sheet reference that includes Sheet 2 Columns A:D (lookup + all possible return columns). Name it, for example: {Sheet2_Table} (covers A:D)

Put this in Sheet 1 “Due Date” (row formula):-
=IFERROR(
IF([Column B]@row = "PU",
VLOOKUP([Column A]@row , {Sheet2_Table}, 2, false),
IF(AND([Column B]@row = "New", [Column C]@row = "photo"),
VLOOKUP([Column A]@row , {Sheet2_Table}, 3, false),
IF(AND([Column B]@row = "New", [Column C]@row = "art"),
VLOOKUP([Column A]@row , {Sheet2_Table}, 4, false),
""
)
)
),
"")

amarkumar

@MRL Replace [Column B] and [Column C] with your actual column names (e.g., [Status]@row , [Type]@row )

All comments

amarkumar

@MRL Yes - this can be done, and VLOOKUP is the cleanest here as long as Sheet 2’s Column A is the lookup key and the return dates are in Columns B, C, and D.

Assumptions :

Sheet 1 has: Column A = key, Column B = status, Column C = type, Due Date = result
Sheet 2 has: Column A = same key, Column B = PU date, Column C = photo date, Column D = art date
Keys in Sheet 2 Column A are unique (or if you are Ok with the “first match wins”)

Create one cross-sheet range

Create a cross-sheet reference that includes Sheet 2 Columns A:D (lookup + all possible return columns). Name it, for example: {Sheet2_Table} (covers A:D)

amarkumar

@MRL Replace [Column B] and [Column C] with your actual column names (e.g., [Status]@row , [Type]@row )

Paul Newcome

The VLOOKUP function is very rigid and comes with a number of limitations as compared to the INDEX function. Having said that, you can use a single VLOOKUP and use an IF statement to determine which number column to pull from.

=VLOOKUP([Column A]@row, {Source Sheet Columns 1 - 4}, IF([Column B]@row = "PU", 2, IF([Column C]@row = "photo", 3, 4)), false)

My suggestion would be to use an INDEX/MATCH and use an IF statement to output the appropriate column setting up separate cross sheet references for each column involved. This will provide more flexibility if the structure of the source sheet ever changes.

=INDEX(IF([Column B]@row = "PU", {2nd Column}, IF([Column C]@row = "photo", {3rd Column}, {4th Column})), MATCH([Column A]@row, {1st Column}, 0))

MRL

Thank you, @amarkumar! This works beautifully

MRL

Thank you, @Paul Newcome, for taking the time to solution and reply. I got an error when I tried to adapt your INDEX formula, but that could have been user error on my end. I was able to get the above IFERROR to work, though.

Paul Newcome

@MRL What was the error you received, and what was the exact formula that you used?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.