Index Match Match

Marie C

I am using this formula for the first time and cannot get past an #invalid value error message.

Here is the formula I am using from Smartsheet's Portfolio template set:

=INDEX({Portfolio Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Project Name]$1, {Portfolio Summary Header Row}, 0))

From what I have read in response to an #invalid value error I should be looking for a data type mis-match but I don't have any. All of the columns are Text/Number except for the source Project ID, which is an auto number (also Text/Number). It is a straight-forward Index/Match and it works from the metadata sheet just fine (part of the template set).

I have checked all reference links and they are pointing to the correct information.

Here is a snapshot of the headings for the Portfolio Summary Data (Column Names and a Header row used in the formula):

And a snapshot of the destination sheet with the formula with matching Column Names, Header Row and column data types - ideally, the Project Name should populate in this sheet:

thanks for any suggestions of where I should look.

Genevieve P.

Hey @Marie C

I agree with Paul that it could be related to the number of columns selected in the first {range}. You'll need to select the same number of columns that's then referenced in the {Portfolio Summary Header Row} reference.

This means if your entire row is selected in that Header Row reference, then yes, your entire sheet and all columns will need to be selected in the first range.

If you've confirmed these ranges are correct, then another reason could be that there's a formula error anywhere in that reference sheet. If even 1 cell contains a formula error, a formula then looking at that column or cell will return the same error.

Let us know if either of this helped!

Cheers,

Genevieve

Genevieve P.

Hey @Marie C

I'm glad that helped! 🙂

Formulas can definitely be tricky, and this is one of the more complex ones - you've done a great job. Feel free to post again when/if you have new formula questions!

Cheers,

Genevieve

Paul Newcome

What column(s) did you select for your first {Range}?

Marie C

Hi Paul. I selected all of the columns, using a sheet range. That was what I interpreted about the structure as it was used in the Smartsheet Portfolio Template set. The template set formula is used between the Intake sheet and populating the individual project metadata sheet. Once the Intake sheet is populated with the project details, the project ID # is copy/pasted into the metadata sheet and the associated data is matched and copied from the Intake sheet,

Genevieve P.

Hey @Marie C

I agree with Paul that it could be related to the number of columns selected in the first {range}. You'll need to select the same number of columns that's then referenced in the {Portfolio Summary Header Row} reference.

This means if your entire row is selected in that Header Row reference, then yes, your entire sheet and all columns will need to be selected in the first range.

If you've confirmed these ranges are correct, then another reason could be that there's a formula error anywhere in that reference sheet. If even 1 cell contains a formula error, a formula then looking at that column or cell will return the same error.

Let us know if either of this helped!

Cheers,

Genevieve

Marie C

Thank you, Genevieve. Your clarification of the range requirements were a great place for me to start. I went back in to the formula and reset the range for the sheet and the header row, to be sure they were identical and the function works perfectly. I somehow must have missed a column or two in my initial set up.

I appreciate both yours and Paul's time in helping me understand this. Admittedly, I am not very good with formulas and experts taking time to help is a great relief.

Genevieve P.

Hey @Marie C

I'm glad that helped! 🙂

Formulas can definitely be tricky, and this is one of the more complex ones - you've done a great job. Feel free to post again when/if you have new formula questions!

Cheers,

Genevieve