=INDEX MATCH .. formula

Malorie Scaramozi · 10/26/21

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},)

Genevieve P. · 10/27/21

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

SmartSheetUser01 · 11/02/23

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))

Genevieve P. · 11/02/23

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?

SmartSheetUser01 · 11/02/23

Awesome - it works! thanks as always!

=INDEX MATCH .. formula

Answers

Help Article Resources

Categories