Possible ‘IF’ or ‘FILTER’ function required in addition to / replacement of VLOOKUP?

[ollyhughes1982]

Hi all,

Please see screenshots below, re my Excel issue. I currently have a worksheet that gives me a list of parkruns that satisfies all of the conditions described in A2, except for the event (venue) being unique, i.e., the event (venue) hasn’t appeared in then list already. Please can anyone come up with a solution that does exactly the same thing as currently, but with the added criteria that it has to be an event (venue) that hasn’t appeared previously.

So, these conditions must be met:

• The value in A1 must appear in the source worksheet
• When found, show the event name and date for the first appearance of that number
• The event (venue) must not have already appeared in the list.

I have also included a link to the file, but it is large and can take a while to open:

parkruns - Golden Wilson Index.xlsx

Current formulas are:

- ‘Event (Venue)’: =IFERROR(VLOOKUP(A4,'All Completed Runs'!$B$4:$E$2003,2,FALSE),"")
- ‘Date Completed’: =IFERROR(VLOOKUP(A4,'All Completed Runs'!$B$4:$E$2003,4,FALSE),"")

Thanks in advance.

Olly.

 

[ollyhughes1982]

Hi all,

I have now resolved this, with the following two formulas:

=IF(A4<>"",INDEX(FILTER('All Completed Runs'!$C$4:$C$2003,('All Completed Runs'!$AO$4:$AO$2003=1)*('All Completed Runs'!$B$4:$B$2003=A4),""),1),"")
=IF(A4<>"",INDEX(FILTER('All Completed Runs'!$E$4:$E$2003,('All Completed Runs'!$AO$4:$AO$2003=1)*('All Completed Runs'!$B$4:$B$2003=A4),""),1),"")

Thanks.