Return value from MATCH function

Hi all,

I've got an INDEX/MATCH error that is stumping me. I have a lat/long identifier that I want to bring into a variety of other sheets. I am trying to match a street address to this identifier for ingestion into the target sheet. As you can see in the reference sheet screen shot, variations of input will have the same identifier (on purpose). The reference sheet is not sorted by address.

Why am I getting a #NO MATCH error with this formula in the target sheet =MATCH("4010 N Borthwick Ave", {ELD Site Identifier Reference Sheet Range 2}, 0) ? (I ultimately want to do this for the whole column, but I'm troubleshooting right now). When I use the default for MATCH (1 for the search type) I get a return, but it's the wrong index (see second image of the output with a return but the 'wrong' answer).

Thank you!

Carrie

Find more posts tagged with

Formulas

Accepted answers

Genevieve P.

Hi @C Leonard

I agree that it should find a match! However I wonder if this has to do with the value in your formula versus the value in your source sheet.

Instead of using MATCH, can you try COUNTIF?

=COUNTIF({ELD Site Identifier Reference Sheet Range 2}, "4010 N Borthwick Ave")

and also

=COUNTIF({ELD Site Identifier Reference Sheet Range 2}, [Site Address]@row)

If these return 0, this helps us know that the issue isn't with Match, it's with the criteria. Let us know the outcome of these tests and I'll be happy to help further!

Cheers,

Genevieve

All comments

Mike TV

@C Leonard

The MATCH formula just by itself is only going to return the row position the first match is found on. It won't return the address or anything else. Was that what you were after?

If that is what you were after, please post a picture of the cross-sheet reference range you've mapped that {ELD Site Identifier Reference Sheet Range 2} to because your formula worked for me and returned a value of "2" which is the correct row position the address is on for my test sheet.

C Leonard

Thanks Mike. The rows in question that I showed in the screenshot are 634 and 635 out of 994. (I don't think a screen shot will help because I can't fit it on a screen). When I run the MATCH function with the Borthwick specific input with the default it returns a value of 9 -- and the address in row 9 isn't at all close to what I'm trying to match.

Ultimately I want to nest this MATCH function within another, but it's where the error is happening so I'm trying to troubleshoot it specifically right now.

Thanks again!

Mike TV

@C Leonard

Yes, you should be able to provide a screenshot that looks something like this:

What I'm trying to determine is that you've mapped your {ELD Site Identifier Reference Sheet Range 2} to the correct column because from the problems you're seeing the chances are good that you haven't mapped the reference correctly.

C Leonard

Got it. Here's a screen shot of the reference sheet and the selected range in my formula.

C Leonard

Looks like I stumped the forum? At least you all are helping to validate that something weird is going on :).

Carrie

Genevieve P.

Hi @C Leonard

I agree that it should find a match! However I wonder if this has to do with the value in your formula versus the value in your source sheet.

Instead of using MATCH, can you try COUNTIF?

=COUNTIF({ELD Site Identifier Reference Sheet Range 2}, "4010 N Borthwick Ave")

and also

=COUNTIF({ELD Site Identifier Reference Sheet Range 2}, [Site Address]@row)

If these return 0, this helps us know that the issue isn't with Match, it's with the criteria. Let us know the outcome of these tests and I'll be happy to help further!

Cheers,

Genevieve

C Leonard

Oh my gosh @Genevieve P. -- nothing like coming to work Monday morning and figuring out that for some reason you had two sheets with the same name, one with incomplete information, that I was using for my cross reference. However, your suggestion to check the count is what helped me troubleshoot and get ride of the bad data and move forward.

It's all working now! Thank goodness.

Genevieve P.

Haha welcome to Monday! I'm glad you sorted it out and I hope this sets you up for a productive week. 🙂

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.