=INDEX MATCH .. formula

Trying to create a standardized process for setting up our construction sheet, contract, and change orders. I would like to be able to pull the client's contact info from one sheet ('Sheet Names') and have that information auto-populate back onto it's specific construction sheet, based on the project's address which will be typed into a cell. I don't even know if I explained that right or if INDEX/MATCH formula is the best plan of action for this... I tried a call with support and an hour later; I still cannot get it to work.

I would like the formula to refer to the entire column so I can 'file save as new' this sheet again and again on different addresses/projects.


=INDEX({sheet names Range 1}, MATCH(Address@row), {sheet names Range 4},)

Answers

  • Hi @Malorie Scaramozi

    It sounds like an INDEX(MATCH will work for you, as long as the address is exactly the same in both sheets!

    An INDEX(MATCH works like this:

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

    You'll notice that the Match requires two values separated by a comma, instead of how you have a ) between the Address and {range}.


    Your syntax is just a little off, try this:

    =INDEX({sheet names Range 1}, MATCH(Address@row, {sheet names Range 4}, 0))


    Let me know if this works for you!

    Cheers,

    Genevieve

  • This formula worked for me as I'm returning a specific date column but I also want to add one month to this formula results? Can you please help? Thanks!


    =INDEX([Estimated Completion Date]1:[Estimated Completion Date]5, MATCH([Task Name]@row, [Task Name]1:[Task Name]5, 0))

  • Hey @SmartSheetUser01

    The easiest way to do this is to simply add 30 days to the end result:

    =INDEX([Estimated Completion Date]1:[Estimated Completion Date]5, MATCH([Task Name]@row, [Task Name]1:[Task Name]5, 0)) + 30

    Would that work for you?

  • Awesome - it works! thanks as always!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!