Index Match Formula Help

Newbie struggling with a formula. I have two sheets. In the main sheet the Intake Number is auto populated. In the second sheet, staff will input the Intake Number- the intake numbers may not be in consecutive order on this sheet as a result like they are on the main sheet. They will also provide notes on the case (column titled: Investigator Updates) or attachments.

I want to create a formula that matches the Intake Number in both sheets and then returns the Investigator Updates to the main sheet on the correct row.

Everything I am trying with Match and Index isn't giving me anything!

(and can I return attachments to the main sheet?- what would I do for that?)

Main Sheet

Sheet 2


Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @beckyb27

    Can you post screen captures of how you're setting up the Index(Match formula (what columns are you selecting)?

    An INDEX(MATCH works like this:

    =INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match in the other sheet}, 0))

    In your instance, you'll need to make sure that 0 is there at the end of the MATCH function because it identifies that the values aren't sorted in the other sheet.

    So, it would be something like this:

    =INDEX({Investigator Updates Sheet 2}, MATCH([Intake Number]@row, {Intake Number Sheet 2}, 0))

    Let me know if this is returning an error or an incorrect result!

    Cheers,

    Genevieve

  • beckyb27
    beckyb27 ✭✭✭✭

    Thank you so much! Your guidance got me there! I got it to work with this formula:

    =IFERROR(INDEX({FY22 Hotline -Updates Range 1}, MATCH([Intake Number]@row, {FY22 Hotline -Updates Range 2}, 0)), "")

  • Genevieve P.
    Genevieve P. Employee Admin

    Wonderful! I'm glad I could help 🙂

  • Jake Gustafson
    Jake Gustafson ✭✭✭✭✭✭

    @Genevieve P. how is the INDEX + MATCH function combination better to use than VLOOKUP? I'm trying to get a better understanding of how/when to use INDEX/MATCH over VLOOKUP and why.

    Essentially, wouldn't the following VLOOKUP do the same as @beckyb27's formula above? @beckyb27 is there any reason why you went the route you did over VLOOKUP?

    =IFERROR(INDEX([Intake Number]@row,{FY Hotline -Updates Range 3}, 3, FALSE),"N/A")

    Where

    • [Intake Number]@row is the Intake Number you are looking for any Investigator Notes in the other Sheet for
    • {FY Hotline -Updates Range 3} is the reference to Sheet 2 where those notes are added
    • 3 is not the right column number to reference because there appears to be hidden columns, but the screen shot makes it look like it's the third column.

    Then the returned value would be the Investigator Update values corresponding to the Intake Number.

    Thanks for whatever bits of knowledge can be dropped on me here to expand my knowledge on this area of functions.

  • Jake Gustafson
    Jake Gustafson ✭✭✭✭✭✭

    Thank you @Genevieve P. that clarifies things much better than what I was understanding before. That's where the efficiency piece comes in to avoid processing across multiple columns. Thanks. I will try to work that into my repertoire to get more comfortable with it.

  • Genevieve P.
    Genevieve P. Employee Admin

    No problem 🙂

  • Nancy Skoulphong
    Nancy Skoulphong ✭✭✭✭

    Hi @Genevieve P. Along with the same question, can you help take a look at this formula? I am getting an #incorrect argument set. Let me know if you need additional information. Thank you in advance for your help.

    =IFERROR(INDEX({RichardWallach}, MATCH({RichardAppNo}, [Applicant Number]@row), 0), "")

    I am collecting a score from Richard's sheet and populating it onto Sheet A. If the Application Number on Richard's sheet match with the application number on Sheet A, populate the score on Sheet A. If no match, the result should be a blank. The property on all the columns is Text/Number.

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 11/24/21

    Hi @Nancy Skoulphong

    A couple of details here in the MATCH function that just need to be swapped around.

    Firstly, you want to list the Value and then the Range: [Applicant Number]@row before {RichardAppNo}, like so:

    MATCH([Applicant Number]@row, {RichardAppNo}

    Then secondly, the 0 should be part of the MATCH function, so inside the ) instead of outside, like so:

    MATCH([Applicant Number]@row, {RichardAppNo}, 0)


    Try the full formula:

    =IFERROR(INDEX({RichardWallach}, MATCH([Applicant Number]@row, {RichardAppNo}, 0)), "")


    Let me know if that works for you!

    Cheers,

    Genevieve

  • Nancy Skoulphong
    Nancy Skoulphong ✭✭✭✭

    Hi @Genevieve P. You are awesome! Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!