Index Match (?) to two different sheets (unique values)

Hi - I am not sure what is the correct formula to use - I have tried a few and I get errors, so would appreciate some assistance. I have been using Cell Linking to pull in the data, but due to the volume it has become cumbersome and unreliable.

I have a master project plan with unique Site IDs in the primary column, and the site details are split between two client sheets - one for each brand; also using the same unique Site IDs in the primary columns. The client sheets are very large, so when trying to select a range, I receive an error that I have exceeded the range limits if the columns are too far apart.

I need the right formula to look up and return the information from either sheet - like a VLOOKUP but returning the data from one of two places.

Example: Site ID 12345 (primary column) I need the activation date to pull in from either brand A or brand B, however on the brand A sheet the activation date is column 5 and on brand B it is column 15.

What is the best type of formula to use?

Find more posts tagged with

Formulas

Project Management

Accepted answers

Julie Fortney

Thanks for the screen shots!

I suggest adding two helper columns to your master sheet, one to pull in Brand A date, and one to pull in Brand B date. Formulas are listed below:

Brand A Activation Date: =IFERROR(INDEX({Brand A - Scheduled Date}, MATCH([Site ID]@row, {Brand A - Site ID}, 0)), "")

Brand B Activation Date: =IFERROR(INDEX({Brand B - Activation Date}, MATCH([Site ID]@row, {Brand B - Site ID}, 0)), "")

Scheduled RevIQ Activation Date: =IF([Brand A Activation Date]@row <> "", [Brand A Activation Date]@row, [Brand B Activation Date]@row)

The Brand A and Brand B columns include cross-sheet references. When you copy the formulas in, you'll need to highlight the sections in { } brackets and click "Edit Reference" to select the columns in your Brand A and Brand B sheets.

I hope that helps!

Julie Fortney

That is interesting... Is there anything different about how the Site ID for that one with the 2/21/23 date is formatted on either the O source sheet or the master sheet?

It seems like the formula isn't able to find a match for the other Site IDs on the other sheets.

All comments

Julie Fortney

Hi @SMacey, Index/Match is almost always a better option than VLOOKUP, so you are on the right track.

If you'd like help with the formula(s), feel free to post screenshots of the 3 sheets and I'd be glad to help.

www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt

SMacey

Thank so much @Julie Fortney - I have been messing with formulas so much today I have gone cross-eyed! I thought Index/Match was the way to go, but just can't seem to get any formula right after hours of tweaking.

Here is my sheet, where I am trying to bring in the data - the column I am trying to populate is the Scheduled Activation Date in "salmon". The primary column, "Site ID" is what I am trying to use to match, and it is unique and will be located in one of the two brand sheets below - for this example we can use the first one of 10979:

Here is a shot of Brand A (where the Site ID is not found - as they are part of Brand B) - note that there are TWO columns between Site & Site Status that are behind the freeze line - I am not sure if there are others that the client may have hidden, if that matters:

And here is the shot of Brand B where we can see 10979 listed, and therefore I am looking for the formula to pull in the associated date (1/30/23) - again to note there are slightly different columns between Brand B and Brand A:

Any advice would be much appreciated! Thank you in advance :)

Julie Fortney

Thanks for the screen shots!

I suggest adding two helper columns to your master sheet, one to pull in Brand A date, and one to pull in Brand B date. Formulas are listed below:

Brand A Activation Date: =IFERROR(INDEX({Brand A - Scheduled Date}, MATCH([Site ID]@row, {Brand A - Site ID}, 0)), "")

Brand B Activation Date: =IFERROR(INDEX({Brand B - Activation Date}, MATCH([Site ID]@row, {Brand B - Site ID}, 0)), "")

Scheduled RevIQ Activation Date: =IF([Brand A Activation Date]@row <> "", [Brand A Activation Date]@row, [Brand B Activation Date]@row)

I hope that helps!

SMacey

Thank you so much @Julie Fortney!

Great idea on the helper columns! However, the formulas are returning blank cells, and every here and there one gives an error saying #INVALID COLUMN VALUE - did I do something wrong?

Julie Fortney

Are your Go Live columns and the Scheduled Date columns on your A & B sheets all set as date columns?

SMacey

Hi @Julie Fortney I updated my Go Live columns to dates, so that fixed the invalid column value error, but still most of the cells are returning blanks. The A&B sheets are managed by the client, but I would assume the Scheduled Date columns are set as dates. Again, really appreciate your help!

Julie Fortney

That is interesting... Is there anything different about how the Site ID for that one with the 2/21/23 date is formatted on either the O source sheet or the master sheet?

It seems like the formula isn't able to find a match for the other Site IDs on the other sheets.

SMacey

Ah ha! They were blank because they are indeed...blank. I didn't even consider the most logical reason! It works!

Thank you SO MUCH @Julie Fortney - you are a lifesaver! And this will save me so much time moving forward as well since I won't have to set up cell linking. I really appreciate your assistance!

Julie Fortney

I'm glad to help! I learned how to use Index/Match through the Community, so I'm glad to pass it on!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.