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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

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

Board Regularmmmm Pizza
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,402
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,383
Messages
5,528,385
Members
409,817
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top