Index/Match situation.

Hi, I apologize in advance, but I've been searching, and trying to figure out how to do this, and I guess I am just not understanding things (or it's not possible).

I have 2 sheets. One is a checklist for all new stores. The other is a checklist for the IT installation of stores for a particular week (though I'm not sure I wouldn't want that to be one giant sheet, that's not important to the issue).

I would like each cell (and I don't believe I can do a column formula because of some of the other rows) to reference it's column in the it install checklist, and pull back the status for that row.

Does that make sense?

I'm pretty sure I can figure out how to pull the incident info for the notes cell if I can get the status info. If necessary, I could name the columns in the IT Install list with the leading zero that is in the Store # of the parent row, if that will make it easier.

1,000,000 thanks in advance! This community has been very helpful in the past!

Meg

Screen shot of a section of the New Store Checklist section for IT Install:

Screen shot of the IT Install Checklist:

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Ok. So you are going to need an INDEX/MATCH/MATCH.

=INDEX(Reference Sheet All Columns}, MATCH([Store #]@row, {Reference Sheet Task Column}, 0), MATCH(INDEX(ANCESTORS([Store #]@row), 1), {Reference Sheet Row 1}, 0))

Paul Newcome

You should have 3 different ranges. The first one covers all columns, the second one covers only the column you are matching in, and the third one is only the top row.

All comments

Paul Newcome

Which sheet of your two screenshots will contain the formula that is looking up from the other?

megmeyer

Sorry. That was kind of silly of me not to specify! The checklist (with the line in blue for the store) needs to lookup the cells in the other sheet.

The whole reason for this is that when I do multiple new stores at a time, I don't work well with the veritical hierarchy of SmartSheet. I like the columns to be the store#, and the steps. I wanted to use check boxes, but then I couldn't figure out a way to put the one or two text fields in the same column. Using the words is just fine.

So, in my screen shots, the Status cell next to 01 - Validate Server ...., should lookup the status for that store (1496) in the other sheet for 01 - Validate Server .... I named the tasks the same, hoping that would make the look up easier.

Thank you!

Meg

Paul Newcome

Ok. So you are going to need an INDEX/MATCH/MATCH.

=INDEX(Reference Sheet All Columns}, MATCH([Store #]@row, {Reference Sheet Task Column}, 0), MATCH(INDEX(ANCESTORS([Store #]@row), 1), {Reference Sheet Row 1}, 0))

megmeyer

Thank you so much!

So, I've been playing with this, trying to get it to work, and I got it to lookup something, but for unkown reasons, it is pulling back the 13th row of the column in the IT Install sheet. I verified that by changing the value in the IT Install Sheet from 11, to 55 (I gave up on words, since it was too hard to figure out which one it was grabbing).

This is the formula, with my edits to the range. The range is all the columns in the IT Install sheet.

=INDEX({231116 IT Install Checklist Range 1}, MATCH([Store #]@row, {231116 IT Install Checklist Range 1}, 0), MATCH(INDEX(ANCESTORS([Store #]@row), 1), {231116 IT Install Checklist Range 1}, 0))

Any ideas? I will continue playing with it myself, but I'm really just throwing darts at a dart board (and I really am bad at darts :) )

Meg

Paul Newcome

You should have 3 different ranges. The first one covers all columns, the second one covers only the column you are matching in, and the third one is only the top row.

megmeyer

I think I misunderstood some parts. I am trying to make separate references for each thing. But I'm not clear on what you are referring to in your formula example as {Reference Sheet Row 1}, 0).

Sorry for my confusion.

megmeyer

No matter what the data is? Seems odd, but I will try it! Thanks again for the quick and helpful responses. I will let you know!

megmeyer

So, I tried this: (after much searching for a missing ")" !)

=INDEX({231116 IT Install Checklist Sheet}, MATCH([Store #]@row, {231116 IT Install Tasks}, 0), MATCH(INDEX(ANCESTORS([Store #]@row), 1), {231116 IT Install Row 1}, 0))

IT Install Checklist Sheet is the whole sheet, IT Install Tasks is the Task column, and IT Install Row 1 is the first row.

I'm getting #NO MATCH.

Paul Newcome

Have you confirmed that there is in fact an exact match on both the Store Number as well as the Task?

megmeyer

Hmmm. They LOOK identical. I copied the tasks from the Main sheet, so they should be exact. The store numbers in the main sheet are all just 5-digit (with leading zeros) which is what I typed in to the IT Install sheet.

A vlookup found the task, but I don't know how to look up a column name to verify that.

Paul Newcome

What does this do?

=MATCH(INDEX(ANCESTORS([Store #]@row), 1), {231116 IT Install Row 1}, 0)

megmeyer

It just shows #REF.

Would it help if I uploaded a section of the two sheets? (I can send the entire install checklist, and just the store 1496 portion of the main one)

Paul Newcome

Ok. And when you click into the cell to edit it, is there a portion of the formula that says that same thing? Is that particular error anywhere within any cell being referenced by the formula?

megmeyer

Now it shows #NOMATCH (might have had a typo?

If I do =ANCESTORS([Store #]@row), I get Invalid Column Name. Shouldn't that give me 01496? (in this particular case) from any cell I type it in? Or does Ancestors have to be part of a larger formula?

Thank you for your patience and help. I can't figure this one out. :(

Paul Newcome

What does this one do?

=INDEX(ANCESTORS([Store #]@row), 1)

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.