Index Match error when trying to return values not previously returned

[Jezza1123]

Hi Guys,

I am using the following formula:

{=INDEX(A!$B$2:$B$5000,MATCH(2,(A!$C$2:$C$5000>'New Companies'!$C$2)*1+('New Companies'!$C$3>A!$C$2:$C$5000)*1-(A!$B$2:$B$5000='New Companies'!B$5:B5)*1,0))}


A!$B$2:$B$5000 = A range of company names
A!$C$2:$C$5000 = A range of incorporation dates of companies
'New Companies'!$C$2 = The start of a date range
'New Companies'!$C$3 = The end of the date range
'New Companies'!B$5:B5 = The names of previously returned companies

The goal is to return a list of companies incorporated between the relevant dates.

You might ask:

• Why not just filter by date? Answer: There is one tab for each letter that the company starts with. I also want a "home screen" where I can glance and see the information without having to do anything.
• Why not put all the data into a single tab? Answer: The data pulls from a website and the website only allows you to access the information letter by letter.
• Why not pull the data from the site on the same tab? Answer: The number of companies changes.

Hopefully that eliminates some questions, but if you know a why to make it work despite my perception of the problem please let me know.

So, the first part of the formula works fine. It pulls the first company and if there was only one I would be done. Obviously there can be more than one. So my idea was to look up instances where the companies pulled are the same as those being searched, if they are the formula should return -1 for that part of the array reducing the total to 1, thereby making the match false.

When I evaluated the formula it turns out that instead of returning false when the names are being compared and they don't match, the formula is returning #N/A. This screws everything up and "0" is returned. Interestingly this only happens when it is comparing 2 or more values with the main listing. IE the following is the output:


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A1 Company[/TD]
[TD]{=INDEX(A!$B$2:$B$5000,MATCH(2,(A!$C$2:$C$5000>'New Companies'!$C$2)*1+('New Companies'!$C$3>A!$C$2:$C$5000)*1,0))}[/TD]
[/TR]
[TR]
[TD]ABC Company[/TD]
[TD]{=INDEX(A!$B$2:$B$5000,MATCH(2,(A!$C$2:$C$5000>'New Companies'!$C$2)*1+('New Companies'!$C$3>A!$C$2:$C$5000)*1-(A!$B$2:$B$5000='New Companies'!B$5:B5)*1,0))}[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]Same as above[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]Same as above[/TD]
[/TR]
</tbody>[/TABLE]

Does anyone have an experience on this or know of an alternate solution?

Thanks

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

Assuming you are putting the first formula in B6 of sheet New Companies, maybe this (Excel 2007 or higher)

B6
=IFERROR(INDEX(A!$B:$B,SMALL(IF(A!$C$2:$C$5000>$C$2,IF($C$3>A!$C$2:$C$5000,IF(ISNA(MATCH(A!$B$2:$B$5000,B$5:$B5,0)),ROW($B$2:$B$5000)))),1)),"")
confirmed with Ctrl+Shift+Enter

copy down

M.

 

[Jezza1123]

Success. Thanks.

Any chance you can explain what this formula is doing?

 

[markmzz]

Hi Guys,

I am using the following formula:

{=INDEX(A!$B$2:$B$5000,MATCH(2,(A!$C$2:$C$5000>'New Companies'!$C$2)*1+('New Companies'!$C$3>A!$C$2:$C$5000)*1-(A!$B$2:$B$5000='New Companies'!B$5:B5)*1,0))}


A!$B$2:$B$5000 = A range of company names
A!$C$2:$C$5000 = A range of incorporation dates of companies
'New Companies'!$C$2 = The start of a date range
'New Companies'!$C$3 = The end of the date range
'New Companies'!B$5:B5 = The names of previously returned companies

Another way (with a small modification in your formula):

Use this formula (use only Enter):

=IFERROR(
INDEX(A!$B$2:$B$5000,
MATCH(0,INDEX(COUNTIF('New Companies'!B$5:B5,A!$B$2:$B$5000)+(A!$C$2:$C$5000<='New Companies'!$C$2)+('New Companies'!$C$3<=A!$C$2:$C$5000),),0)),
"")

Markmzz

 

[Marcelo Branco]

Success. Thanks.

Any chance you can explain what this formula is doing?

Try the tool Formulas > Evaluate formula

An alternative formula, following your logic in #1, would be

=INDEX(A!$B$2:$B$5000,MATCH(2,(A!$C$2:$C$5000>$C$2)+($C$3>A!$C$2:$C$5000)-ISNUMBER(MATCH(A!$B$2:$B$5000,B$5:B5,0)),0))
Ctrl+Shift+Enter

Note:
1. you don't need to multiply by 1 because the + (addition) coerces the logical (Boolean) values, False/True, to respectively 0/1.
2. to exclude the companies already returned you should use -ISNUMBER(MATCH(A!$B$2:$B$5000,B$5:B5,0))

M.

 

[markmzz]

A small modification in my last formula:

(use only Enter):
=IFERROR(
INDEX(A!$B$2:$B$5000,
MATCH(0,INDEX(COUNTIF(B$5:B5,A!$B$2:$B$5000)+(A!$C$2:$C$5000<=$C$2)+($C$3<=A!$C$2:$C$5000),),0)),
"")

Or

(use Ctrl+Shift+Enter)
=IFERROR(
INDEX(A!$B$2:$B$5000,
MATCH(0,COUNTIF(B$5:B5,A!$B$2:$B$5000)+(A!$C$2:$C$5000<=$C$2)+($C$3<=A!$C$2:$C$5000),0)),
"")

Markmzz