Index Match problem

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
688
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
688
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))<>
 
Last edited:

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,672
Hi

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

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
688

ADVERTISEMENT

Thanks

Code:
=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

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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

Well-known Member
Joined
Mar 15, 2011
Messages
688

ADVERTISEMENT

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
Code:
=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

Well-known Member
Joined
Mar 15, 2011
Messages
688
Ok, so I'm not sure if this is what you meant but I tried

Code:
=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
 

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
688
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.
 

Forum statistics

Threads
1,141,140
Messages
5,704,522
Members
421,353
Latest member
jekoxien15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top