Help With Index/Match!?

Neil Fleming

Man, this is frustrating! I've devoted a fair bit of time perusing all of the posts, but haven't been able to get this right yet.

I've created a couple of sheets to act as training examples...the main one having products in one column, and an attempted INDEX/MATCH by month (* Sales) in another 3 columns. I've created a main "Test" sheet, and an associated "Test Validation sheet" that contains the data to be looked up.

In the first section of the main "Test" sheet I've created a Vlookup to return the Product Category. Works fine. Also in this first section I can do a Vlookup for March, April, and May in the "Validation" sheet and multiply the value by "Sales". Again, works fine.

In the second section of the main "Test" sheet I was able to ensure that MATCH worked to return a row number, as needed. Works fine. Again, this is a single-column MATCH.

In the third section, I can successfully do a single-column INDEX/MATCH with hard-coded column to return the Product Category.

I have been unsuccessful in doing a full relative INDEX/MATCH based on the Product and the Month, where both values are not hard-coded. Help?

See attached screenshots of both sheets.

L_123

It sounds to me like you need an index(Match Match)

Index has a third value that you can use as a column reference that is optional.

=index(March1:December1000,match([Product Category]@row,{product category},0),match(March$1,{March1:Dec1},0))*Sales

Note: its generally easier to use the number correlating to the month rather than the text, it could get rid of some of your helper information if you are looking to optimize this.

Genevieve P.

Hi @Neil Fleming

Would you be able to be more specific about where you are running into issues? Are you receiving an error message?

It sounds like you've figured out how to combine an INDEX/MATCH, but I'll spell it out here just in case this helps:

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

So in your instance:

=INDEX({March Column}, MATCH(Product@row, {Product Column}), 1)

Then you can multiply this value by what's in your Sales column:

=INDEX({March Column}, MATCH(Product@row, {Product Column}), 1) * Sales@row

If you were looking to match multiple criteria (such as the Product Category as well) you could use an INDEX(COLLECT. Paul has a great outline of this in another Community post (see here).

Let me know if this works for you! If not, it would be useful to see a screen capture of your {references} in the reference window that pops up, along with any error messages you're receiving.

Cheers,

Genevieve

Neil Fleming

Many thanks for the help, Genevieve. Your example here shows the root of my problem:

INDEX({March Column}, MATCH(Product@row, {Product Column}), 1) * Sales@row

Notice it says "INDEX({March Column}". That requires prior knowledge of the column involved. What I am trying to do is to return the intersection based both on the rows (product) and the columns (month). (Intersection within a matrix). I've figured out half of this...locating via MATCH the product row, but I cannot seem to locate the desired column within the matrix using MATCH.

In Excel, the formula for location in a 2-dimensional matrix is simply:

INDEX(data range, MATCH(val, rows,1), MATCH(val, columns, 1))

or even using VLOOKUP along with MATCH as:

"VLOOKUP(lookup_value, table, MATCH(col_name,col_headers,0),0)"

It's doing the match of the month across multiple columns that I am not doing right.

L_123

It sounds to me like you need an index(Match Match)

Index has a third value that you can use as a column reference that is optional.

=index(March1:December1000,match([Product Category]@row,{product category},0),match(March$1,{March1:Dec1},0))*Sales

Note: its generally easier to use the number correlating to the month rather than the text, it could get rid of some of your helper information if you are looking to optimize this.

L_123

Lol didn't see your most recent reply. It is the same way in smartsheet as excel, same format same everything.

Neil Fleming

Many thanks! I'd been looking a long time and each article/guide said you couldn't do an index/match/match. Is this a new thing? (I'm only a couple of days into this Smartsheet stuff).

Seems similar, but not identical.

I notice that it is perfectly happy referencing the product code (e.g., NF01) for the row match. That's obviously a text value.

As you said, it won't work with "March, April, May" and instead wants numbers for the column reference. Why won't it work with numbers? Also what's a helper column, and how would you use one? So, it worked when I substituted numbers for months on both the main sheet and the reference sheet, and it worked! Hooray!

Neil Fleming

I used:

=INDEX($Product$23:$April$27, MATCH($Product15, $Product$23:$Product$27, 1), MATCH(March$1, $Product$23:$April$23, 1))

To do this I kept the reference data on the same sheet for ease of use. Now I'll modify it to work with the reference sheet.

Neil Fleming

For that, I used this formula and it worked:

=INDEX($Product$30:$April$34, MATCH($Product21, {Neil's New Test Validation Range 5}, 1), MATCH(March$1, {Neil's New Test Validation Range 6}, 1))

Do you always have to work via named ranges on another sheet, or can you use direct cell references as well?

Thanks again...Neil

L_123

You can do a direct cell reference, but you need a dedicated cell to do it, and it doesn't work on ranges. right click the desired cell to transfer the information and you can create a "cell link". Personally I never use this feature, i just create references