INDEX MATCH problem

[ollyhughes1982]

Hi,

I have the following formula:

=IFERROR(INDEX('All Completed Runs'!$C$4:$C$2003,MATCH("*"&"United Kingdom"&"*"&"*"&A4&"*"&"<>1",'All Completed Runs'!$D$4:$D$2003&'All Completed Runs'!$C$4:$C$2003&'All Completed Runs'!$FL$4:$FL$2003,0),0),"")

The first two parts work fine, but the third search won't work. This third criteria should look in column ref 'All Completed Runs'!$FL$4:$FL$2003 and if there is a 1 in there not show a value. If there is not a 1 then the value should show. (if there is a 1 in 'All Completed Runs'!$FL$4:$FL$2003 then it is a restricted even and shouldn't be included).

Column ref 'All Completed Runs'!$FL$4:$FL$2003 displays as either blank ("") or a 1, according to the formula in that column.

Is there something about the <>1 that is not right? is it nor possible to do a 'not equal to' search within INDEX(MATCH?

Thanks in advance,

Olly.

 

[MEUserII]

Hi,

I have the following formula:

=IFERROR(INDEX('All Completed Runs'!$C$4:$C$2003,MATCH("*"&"United Kingdom"&"*"&"*"&A4&"*"&"<>1",'All Completed Runs'!$D$4:$D$2003&'All Completed Runs'!$C$4:$C$2003&'All Completed Runs'!$FL$4:$FL$2003,0),0),"")

The first two parts work fine, but the third search won't work. This third criteria should look in column ref 'All Completed Runs'!$FL$4:$FL$2003 and if there is a 1 in there not show a value. If there is not a 1 then the value should show. (if there is a 1 in 'All Completed Runs'!$FL$4:$FL$2003 then it is a restricted even and shouldn't be included).

Column ref 'All Completed Runs'!$FL$4:$FL$2003 displays as either blank ("") or a 1, according to the formula in that column.

Is there something about the <>1 that is not right? is it nor possible to do a 'not equal to' search within INDEX(MATCH?

Thanks in advance,

Olly.

Without looking at your original data set; I have a question: in your MATCH function's lookup value you have a double wildcard ("*"&"*") - why?
One wildcard entry should be enough to pickup whatever comes after "United Kingdom" but before the value in A4 as it is my understanding that wildcards ("*") can pick up single character strings or multi-character strings.
In anycase I've rewritten your above formula below, try and see if it works, and let me know.
=IFERROR((INDEX(('All Completed Runs'!$C$4:$C$2003), (MATCH((("*"&"United Kingdom"&"*"&A4&"*")<>(1) ), (('All Completed Runs'!$D$4:$D$2003)&('All Completed Runs'!$C$4:$C$2003)&('All Completed Runs'!$FL$4:$FL$2003) ), (0) ) ), (0) ) ), ("") )

 

[ollyhughes1982]

Hi.

Thanks for looking this. Not sure why I used the wildcards in that way at the time, I did these ones quite a while a ago.

I didn't attach original dataset as it is a massive workbook with lots of interlinked worksheets. I can provide a link if you like, but it can take a long time to open.

Just popping out a moment, so will try this as soon as I get back.

Thanks again.

 

[ollyhughes1982]

Without looking at your original data set; I have a question: in your MATCH function's lookup value you have a double wildcard ("*"&"*") - why?
One wildcard entry should be enough to pickup whatever comes after "United Kingdom" but before the value in A4 as it is my understanding that wildcards ("*") can pick up single character strings or multi-character strings.
In anycase I've rewritten your above formula below, try and see if it works, and let me know.
=IFERROR((INDEX(('All Completed Runs'!$C$4:$C$2003), (MATCH((("*"&"United Kingdom"&"*"&A4&"*")<>(1) ), (('All Completed Runs'!$D$4:$D$2003)&('All Completed Runs'!$C$4:$C$2003)&('All Completed Runs'!$FL$4:$FL$2003) ), (0) ) ), (0) ) ), ("") )

Hi, it didn't work, unfortunately. I can give a link to the file, but it is quite large and can take a few minutes to open. It's 57mb in size. I have included a screenshot.

OneDrive: parkrun - My parkrun Record (Oliver Hughes - A132324).xlsx
Dropbox: parkrun - My parkrun Record (Oliver Hughes - A132324).xlsx

Thanks.

 

[Fluff]

Maybe

=FILTER('All Completed Runs'!$C$4:$C$2003,('All Completed Runs'!$D$4:$D$2003="United Kingdom")*(ISNUMBER(SEARCH(A4,'All Completed Runs'!$C$4:$C$2003)))*('All Completed Runs'!$FL$4:$FL$2003<>1),"")

 

[ollyhughes1982]

I think I have now solved this with an XLOOKUP instead:

=IFERROR(IF(XLOOKUP("*Bushy*",'All Completed Runs'!$C$4:$C$2003,'All Completed Runs'!$FL$4:$FL$2003,"",2)<>1,XLOOKUP("*Bushy*",'All Completed Runs'!$C$4:$C$2003,'All Completed Runs'!$C$4:$C$2003,"",2),""),"")

Thanks.

 

[ollyhughes1982]

Maybe

=FILTER('All Completed Runs'!$C$4:$C$2003,('All Completed Runs'!$D$4:$D$2003="United Kingdom")*(ISNUMBER(SEARCH(A4,'All Completed Runs'!$C$4:$C$2003)))*('All Completed Runs'!$FL$4:$FL$2003<>1),"")

Thanks. I'll try this as well.

 

[ollyhughes1982]

Thanks. I'll try this as well.

With this solution, does it just show the first result? I only want to show the first, will I need to add a SEQUENCE? How would that look?

 

[Fluff]

If you can multiple results this will return the first

=index(FILTER('All Completed Runs'!$C$4:$C$2003,('All Completed Runs'!$D$4:$D$2003="United Kingdom")*(ISNUMBER(SEARCH(A4,'All Completed Runs'!$C$4:$C$2003)))*('All Completed Runs'!$FL$4:$FL$2003<>1),""),1)

 

[ollyhughes1982]

If you can multiple results this will return the first

=index(FILTER('All Completed Runs'!$C$4:$C$2003,('All Completed Runs'!$D$4:$D$2003="United Kingdom")*(ISNUMBER(SEARCH(A4,'All Completed Runs'!$C$4:$C$2003)))*('All Completed Runs'!$FL$4:$FL$2003<>1),""),1)

Brilliant, that is what I was after. Thanks so much. I feel like I owe you a crate of beers for your help over the past 18 months or so! So helpful and patient with my lack of knowledge!