Return value at the intersection of a row and column

bob865

I am new to smartsheet and haven't found what I'm looking for through search. I have a sheet that is contains a table of cycle times. This table is for reference and the values in the table will be used in a different sheet. I want to be able to return the value that is at the intersection of a row and column based on two values in the main table.

So in this sheet, I have two values, the router step and project type. I want to have the value from the table in the second sheet placed in the cycle time column.

In the second sheet I have a table with the cycle times. So for example, Project 1, I would want 22 in the cycle time column. This value need to be dynamic so either of these values change, the value in the cycle time column would change.

Any help or guidance is appreciated.

bisaacs

Hey @bob865,

I think what you're looking for involves a combination of IF, INDEX, and MATCH:

=IF([Project Type]@row = "Kaizen - Light", INDEX([Kaizen - Light]:[Kaizen - Light], MATCH([Router Step]@row, [Primary Column]:[Primary Column])), IF([Project Type]@row = "Kaizen - Medium", INDEX([Kaizen - Medium]:[Kaizen - Medium], MATCH([Router Step]@row, [Primary Column]:[Primary Column])), IF([Project Type]@row = "Kaizen - Heavy", INDEX([Kaizen - Heavy]:[Kaizen - Heavy], MATCH([Router Step]@row, [Primary Column]:[Primary Column])))))

You'll need to change the cell references for each to actually link to the columns you're requiring, but I think how I labeled things makes sense.

Hope this helps!

bisaacs

Hey @bob865,

I think what you're looking for involves a combination of IF, INDEX, and MATCH:

=IF([Project Type]@row = "Kaizen - Light", INDEX([Kaizen - Light]:[Kaizen - Light], MATCH([Router Step]@row, [Primary Column]:[Primary Column])), IF([Project Type]@row = "Kaizen - Medium", INDEX([Kaizen - Medium]:[Kaizen - Medium], MATCH([Router Step]@row, [Primary Column]:[Primary Column])), IF([Project Type]@row = "Kaizen - Heavy", INDEX([Kaizen - Heavy]:[Kaizen - Heavy], MATCH([Router Step]@row, [Primary Column]:[Primary Column])))))

You'll need to change the cell references for each to actually link to the columns you're requiring, but I think how I labeled things makes sense.

Hope this helps!

bob865

That's great. Thank you. I was I'm trying now to figure out how to match in a row. I thought the match function would do it., but I'm getting weird returns. Some it returns exactly where the search value is, and other times it returns no match when the match is in the list and yet others it returns the wrong column. I even went through and copied verbatim from one sheet to the other to catch any typos and it still returns strange values.

I created the test column to play with the formula. I just wanted it to return the column number that it finds the value in the "Project Type" column in.

I copied down the cell titles so that I would have something to search. The Range in the formula is just that top row. The first two values, Kaizen - Light and Kaizen - Medium, return 2 and 3 like I expect. But when I get to Kaizen - Heavy it returns 1 and not 4 as expected. I copied the value from the main sheet to the reference sheet to eliminate any typo errors.

Any ideas why I getting such strange, to me, results?

bob865

https://community.smartsheet.com/discussion/comment/433258#Comment_433258

I found the answer! I needed to adjust the search type. By adding the argument (,0) to the end of the match function it started returning the values I expected.