Help with an Array Formula (I get #ref error)

[mick0005]

Hi All -

I used the forums and some other help sites to try and come up with an array formula because I was previously using a Vlookup but as more data has been entered into this worksheet, we are encountering the issue where you need to output multiple results on the same search criteria.

So, I have posted the 2 worksheets I am using, one which is the raw data and another where I have attempted to do the array lookup. You can see the formula I used as well as the named ranges that the formula is referencing.

Ideally, it should be outputting the start date from column H in the data sheet. Eventually I want to be able to put another formula next to it that will also output the end date (column I) and next to those, any additional start dates and end dates that it finds a match for in the "ptcoop" range. So I am looking to match all instances of a value in the ptcoop range to all corresponding start and end dates for that value.

Any ideas on why my formula isn't working to output the corresponding "start date"?

 

[Saagar]

Please copy paste the formula in your reply...

 

[mick0005]

Please copy paste the formula in your reply...

=INDEX(startdate, SMALL(IF(A1=ptcoop, ROW(ptcoop)-MIN(ROW(ptcoop))+1, ""), COLUMN(A1)))

 

[Saagar]

In Cell B2 in wksht2, do you want the smallest date in column H of wksht1 for all ptcoop matches with A1?

 

[mick0005]

In Cell B2 in wksht2, do you want the smallest date in column H of wksht1 for all ptcoop matches with A1?

Based on your question I am beginning to think maybe I modeled my formula after another one which won't work in my case.

What I want in B1 is for it to look at A1, reference the ptcoop range, and where it matches, output the start date. In C1 I will want for it to look at A1 and output the next start date that matches A1 from the ptcoop range. I will want to do this for several columns until it finds no more matches in the ptcoop range for A1.

Effectively, list (horizontally) each start date in the "startdate" range that matches A1.

Did I come up with the wrong formula to do that?

 

[Saagar]

Put this formula in B1
=SMALL(IF($A$1=ptcoop,startdate,""),COLUMN(B1)-1)
and hit Ctrl+Shift+Enter for array-mode formula.

Drag it to the cells in right to create the horizontal list.

It will return errors in the following two cases
- If there is no match found for value in A1, it will return #VALUE!
- If there are 5 values in the vertical list and you drag the formula in 10 cells, the last 5 cells in the horizontal list will return #NUM!

Try it out... if it works the way, you want, may be we can fix the error situations to return blanks instead of errors.

 

[mick0005]

Put this formula in B1
=SMALL(IF($A$1=ptcoop,startdate,""),COLUMN(B1)-1)
and hit Ctrl+Shift+Enter for array-mode formula.

Drag it to the cells in right to create the horizontal list.

It will return errors in the following two cases
- If there is no match found for value in A1, it will return #VALUE!
- If there are 5 values in the vertical list and you drag the formula in 10 cells, the last 5 cells in the horizontal list will return #NUM!

Try it out... if it works the way, you want, may be we can fix the error situations to return blanks instead of errors.

I tried your formula and it is still giving me the #REF! error. Does it matter that the named ranges "ptcoop" and "startdate" are on another worksheet within the same workbook? Also the values in the ptcoop range are actually a concatenate of several other columns... will that cause any problem with the index funtion referencing a formula rather than an actual value?

By the way, if it was helpful, I would be happy to email you the actual workbook so you can take a look.

 

[Saagar]

I used the same named ranges to test it out on my end and also put the formula on a different worksheet, worked without any issues. I also made ptcoop range concatenated values and it still worked. Referencing formulas should not give #REF! errors.

The formula I sent does not use "index" function. Did you use my formula as is?

 

[mick0005]

I used the same named ranges to test it out on my end and also put the formula on a different worksheet, worked without any issues. I also made ptcoop range concatenated values and it still worked. Referencing formulas should not give #REF! errors.

The formula I sent does not use "index" function. Did you use my formula as is?

Sorry I didn't mean index function, I meant SMALL. I used the formula exactly as is. I will admit I am certainly outside of my realm here on troubleshooting because I don't know how the small function works, as well as what the remainder of the function is telling excel to do. Any chance you'd take a look at my exact spreadsheet if I sent it to you?

 

[Saagar]

I understand Mick. I am new to "array" formulas and I am learning as well.

I will send you a PM with my email address. I will not be able to test your spreadsheet until later tonight.