Index Match problem

[Noz2k]

Hi, I'm relatively new to excel and have used Index Match to retrieve data from 1 table that matches a certain criteria in order to fill another table.

The problem I am having is that as the amount of data varies dependant on the location inputted by the user excel generates "N/A" for cells where there are no further values that match the criteria.

I have tried using an IFError statement to return "" instead, but because the formula has a clause in it which refers to being > than the above cell it returns the first value again after the first "" cell.

I hope this makes sense to someone, and any help would be appreciated

 

[GlennUK]

Show your formula.

 

[Noz2k]

It's most probably not the neatest formula or the best way of doing things, but it's taken me long enough to generate the right results and other than the N/A's it does for the moment I do still need to make column K only have the be between the day & month of User_Sheet!D1 and to discount the year.

It's




=INDEX(Sheet2!$A$3:$A$20000,MATCH(User_Sheet!$B$1,IF((Sheet2!$A$3:$A$20000>User_Sheet!$A3)*(Sheet2!$K$3:$K$20000>=User_Sheet!$D$1(Sheet2!$K$3:$K$20000<User_Sheet!$E$1),Sheet2!$E4:$E50001),0))
<User_Sheet!$E$1),Sheet2!$E4:$E50001),0))< p></User_Sheet!$E$1),Sheet2!$E4:$E50001),0))<>
<User_Sheet!$E$1),Sheet2!$E4:$E50001),0))< p>(Can't get it to show whole formua?)</User_Sheet!$E$1),Sheet2!$E4:$E50001),0))<>
<User_Sheet!$E$1),Sheet2!$E4:$E50001),0))< p></User_Sheet!$E$1),Sheet2!$E4:$E50001),0))<>
<User_Sheet!$E$1),Sheet2!$E4:$E50001),0))< p> <?XML:NAMESPACE PREFIX = User_Sheet!$E$1),Sheet2!$E4 /><User_Sheet!$E$1),Sheet2!$E4:$E50001),0))< p><User_Sheet!$E$1),Sheet2!$E4:$E50001),0))< p>
as an array where column Sheet2 K is a date value
</User_Sheet!$E$1),Sheet2!$E4:$E50001),0))<>
</User_Sheet!$E$1),Sheet2!$E4:$E50001),0))<>
</User_Sheet!$E$1),Sheet2!$E4:$E50001),0))<>

 

[Sandeep Warrier]

Hi

Post your formula in code tags [CODE][/CODE] or put a space after "<" in your formula.

 

[Noz2k]

Thanks

=INDEX(Sheet2!$A$3:$A$20000,MATCH(User_Sheet!$B$1,IF((Sheet2!$A$3:$A$20000>User_Sheet!$A3)*(Sheet2!$K$3:$K$20000>=User_Sheet!$D$1)*(Sheet2!$K$3:$K$20000< User_Sheet!$E$1),Sheet2!$E4:$E50001),0))

 

[GlennUK]

When you say

I have tried using an IFError statement to return "" instead, but because the formula has a clause in it which refers to being > than the above cell it returns the first value again after the first "" cell.

... if you test the cell above for "", and if so return "", would that do?

 

[Noz2k]

Will give that a try now, have just realised that my formula was slightly wrong anyway, as I changed a few things and forgot to apply the changes to the E column

new formula is

=INDEX(Sheet2!$A$3:$A$20000,MATCH(User_Sheet!$B$1,IF((Sheet2!$A$3:$A$20000>User_Sheet!$A4)*(Sheet2!$K$3:$K$20000>=User_Sheet!$D$1)*(Sheet2!$K$3:$K$20000< User_Sheet!$E$1),Sheet2!$E$3:$E$20000),0))

 

[Noz2k]

Ok, so I'm not sure if this is what you meant but I tried

=IF($A3<>"",INDEX(Sheet2!$A$3:$A$20000,MATCH(User_Sheet!$B$1,IF((Sheet2!$A$3:$A$20000>User_Sheet!$A3)*(Sheet2!$K$3:$K$20000>=User_Sheet!$D$1)*(Sheet2!$K$3:$K$20000< User_Sheet!$E$1),Sheet2!$E$3:$E$20000),0)),"")

but I now get all "N/A" values

 

[GlennUK]

Did you remember to reenter as an array?

 

[Noz2k]

Have just tried it again and not everything is showing N/A but it hasn't made a difference. It's not returning "" at all, and just returning "N/A" for all the values where there are no more matches.