INDEX formula

I am trying to pull in a date based on a specific criteria from another sheet (sheet is called SOW BB28-A tracking). For example, I have an item (Fleet No ) and a warranty start date. I want to pull the warranty date into a master sheet based on the fleet number. I have tried the INDEX formula a million diff ways and its not working for me.

=INDEX({SOW BB28-A Tracking Range 2}, MATCH([Fleet No]@row, {SOW BB28-A range 3}, 0)

I know this should be so easy. What am I missing??

Find more posts tagged with

Accepted answers

All comments

Craig Lemberger

I am not understanding your question exactly, but you must Index a piece of information, if you can match a piece of information on your current sheet with the same information on the sheet you reference. When the info matches, the Indexed info will be pulled over:

Index(date wanted on referenced sheet), match(Fleet no@row, Fleet no on the referenced sheet)

JennS_

I have a fleet number on 2 separate sheets. I want to pull the date that the warranty starts for a specific fleet number and have it update in the other sheet for the matching fleet number. See master sheet below. I am trying to pull the warranty date from second image below, into this sheet where the fleet numbers match.

Paul H

Might just be missing a bracket at the end

=INDEX({SOW BB28-A Tracking Range 2}, MATCH([Fleet No]@row, {SOW BB28-A range 3}, 0))

JennS_

Hi- that did not work for me. I really can't figure it out!

Genevieve P.

Hi @JennS_

You formula syntax is correct - I'd like to try troubleshooting a few different things.

First, check to make sure your two ranges are looking at the right columns, and that the full columns are selected

{SOW BB28-A Tracking Range 2} = Warranty Start Date
{SOW BB28-A range 3} = Fleet No

If that's correct, the next thing to check is that the formula is finding a match for your Fleet No in the current sheet.

=COUNTIF({SOW BB28-A range 3}, [Fleet No]@row)

^This should give you a number. If you're getting 0, then the formula isn't finding a match between those two columns - in this instance we'd need to know what type of columns your two [Fleet No] columns are, and how the number is created/input.

A final note... I see that your formula states [Fleet No]@row but the column name looks to be [Fleet No.] with a period. Make sure that your column name is exactly the same in the formula as in your current sheet 🙂

If this hasn't helped, let us know if you're seeing a formula error message (and which one) or if you're getting an incorrect result!

Thanks,

Genevieve

JennS_

Genevieve - Thank you for your notes. I checked all of these items to the formula below:

=INDEX({SOW BB28-A Warranty Start Date}, MATCH([Fleet No.]@row), {SOW BB28-A Tracking Fleet No}, 0))

The error I get is #UNPARSEABLE

I even tried this:

=INDEX({SOW BB28-A Warranty Start Date}, COUNTIF([Fleet No.]@row), {SOW BB28-A Tracking Fleet No}, 0))

It still isn't working. Once question is are you limited within a sheet to doing this type of formula? For instance, I have another column that also does an index to another sheet. Could that be causing a problem? I have also used linked cells in my master sheet, but I have so many linked cells that I was trying to make it easier to pull information by index formula. Any additional help is appreciated.

Genevieve P.

Hi @JennS_

For your first formula, there's an extra ) after MATCH(___) that should be removed:

=INDEX({SOW BB28-A Warranty Start Date}, MATCH([Fleet No.]@row, {SOW BB28-A Tracking Fleet No}, 0))

For the COUNTIF formula, I meant to test it fully on it's own, simply to see if it's finding a match at all! This is a troubleshooting step, not something for your end formula 🙂

=COUNTIF({SOW BB28-A range 3}, [Fleet No]@row)

But before you do that, see if removing the extra ) made the first formula work! Even if you're using INDEX(MATCH in a different column, you should still be able to use it in this column. Just make sure you're not copy/pasting the same formula and editing references as that will update the reference across the entire sheet. Instead, create new references from scratch to make sure it's looking at the right column.

Let me know if that helps!

JennS_

@Genevieve P.

Okay! I updated the first formula and this time I got #INVALID COLUMN VALUE. Progress !?

And as an FYI- the countif verification did work. It returned a number of 1.

Genevieve P.

Hi @JennS_

That's great that the COUNTIF works! :) That means that it should find a match.

#Invalid Column Value generally means that the column type is different than the value you're putting in it. Can you confirm that both columns are Date type of columns?

{SOW BB28-A Warranty Start Date} and the column where the formula is being input?

JennS_

@Genevieve P.

I just confirmed, and they were not both date columns! So i updated it and it WORKED! I can't believe it was something so simple. I appreciate your help!!! You are the best.

Genevieve P.

No problem at all! I'm glad we got there in the end 🙂

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.