MATCH function return second result

[thesnowplow]

=OFFSET(Sheet1!$A$7,0,MATCH(Sheet1!$A$1,Sheet1!$B$6:$OI$6,0),200,1)

Formula I use as a named range for my chart series works perfectly. How do I modify it to return 2nd result as there are more than one matches that fit the criteria $A$1.

 

[Domenic]

Try...

=OFFSET(Sheet1!$A$7,0,SMALL(IF(Sheet1!$B$6:$OI$6=Sheet1!$A$1,COLUMN(Sheet1!$B$6:$OI$6)-COLUMN(Sheet1!$B$6)+1),2),200,1)

Hope this helps!

 

[thesnowplow]

I tired it and it returns invalid reference error when I try to enter the named range into chart series (e.g. ='Document Name.xlsx'!ChartSeries) though my original formula works fine.

I'm I doing something wrong? Does it need to be entered as array? But you can't enter an array when using named range can you?

 

[Domenic]

Yes, you can definitely use an array formula in a defined name, and there's no need to confirm the formula with CONTROL+SHIFT+ENTER. You simply enter the formula, and click OK. And your syntax (e.g. ='Document Name.xlsx'!ChartSeries) is correct. Make sure that workbook name is spelled correctly, that the file extension is correct, and that the name is also spelled correctly.

 

[thesnowplow]

You're right my bad, I tried it again in a new workbook from scratch and it worked perfectly. Big thanks!

I have set up around 60~ named ranges with this formula replacing the ,2), with numbers from 1-60 and have a drop down in $A$1 so I can rotate though a list of products and it will give me a multi-graph.

However if I have Product1 with 60 results and then switch to Product2 with less than that, some chart series will be empty and it will trigger the "A formula in this worksheet contains one or more invalid references" error. I can click "ok" and the multi-graph will still be okay, but is there any workaround to suppress this error from popping out at all?

 

[Domenic]

First, I do wonder whether it's necessary to have ~60 named ranges. Secondly, a couple of possibilities come to mind with regards to suppressing the error. But I can't be sure whether either of them would be appropriate without some additional information.

Can you provide a small sample of the source data consisting of a few rows and a few columns, but explain how many rows and columns you have in total, and how the drop down relates to the chart?

 

[thesnowplow]

Hope you'll be able to see the link to mock file. Some variables of your formula are slightly amended to fit the mock file scenario. In reality I have around 400 columns. Each column is anywhere from 500-5,000 rows (picked 200 for now in formula but I think I know how I will make it dynamic). And I have over a dozen items in the drop down in $A$1. Each Item in the drop-down will have anywhere from 1-60~ matches. The data itself will change every week but what I described above will be true from week to week.

If you select Product1 you see 3 lines on chart and it's happy days. If you select Product2 it will prompt error, but after that it will correctly display 2 lines on chart. What I want is a way to suppress the error or find a way around the error.

I thought about doing Pivot graph at first but when you have some many columns you will have to manually rearrange fields to swap between products.

https://docs.google.com/file/d/0B-FepBMd3NrVSGpsRXVROEhxMlE/edit?usp=docslist_api&filetype=msexcel

Let me know if you can't access the link.

 

[Domenic]

Sorry, but I don't download files from the Internet. If you post a small sample of data, as I've described in my previous post, I'll have a loot it.

 

[thesnowplow]

http://i.imgur.com/dCusNBP.png

Sorry. I hope this helps. When I switch to Product2 in A1 drop down it will show error because there are only 2 matches in data set but I have 3 chart series.

 

[Domenic]

Oh I see. In that case, I think we can do it this way.

We can define your names so that it will refer to an empty column when a series for a product isn't available. But we would need to define two names for each series. One to point to the column, and the other to refer to the range.

In your example, the data ends at Column I. So let's use Column J for that empty column we need. (I would suggest you shade it in some way or hide it so that it wouldn't be used for anything else.)

So now for each series define two names as follows (note that since Column J is our empty column, we use 9 as the offset when we're defining the names for our series, so you'll need to replace the number 9 accordingly)...

Name:  ChartSeriesCol

Refers to:  =SMALL(IF(Sheet1!$B$6:$OH$6=Sheet1!$A$1,COLUMN(Sheet1!$B$6:$OH$6)-COLUMN(Sheet1!$B$6)+1),1)

Name:  ChartSeries

Refers to:  =OFFSET(Sheet1!$A$7,0,IF(ISNUMBER(ChartSeriesCol),ChartSeriesCol,9),200,1)

Name:  ChartSeries2Col

Refers to:  =SMALL(IF(Sheet1!$B$6:$OH$6=Sheet1!$A$1,COLUMN(Sheet1!$B$6:$OH$6)-COLUMN(Sheet1!$B$6)+1),2)

Name:  ChartSeries2

Refers to:  =OFFSET(Sheet1!$A$7,0,IF(ISNUMBER(ChartSeries2Col),ChartSeries2Col,9),200,1)

Name:  ChartSeries3Col

Refers to:  =SMALL(IF(Sheet1!$B$6:$OH$6=Sheet1!$A$1,COLUMN(Sheet1!$B$6:$OH$6)-COLUMN(Sheet1!$B$6)+1),3)

Name:  ChartSeries3

Refers to:  =OFFSET(Sheet1!$A$7,0,IF(ISNUMBER(ChartSeries3Col),ChartSeries3Col,9),200,1)

...and so on.

Hope this helps!