Finding the second instance with an xlookup

[ECB]

Hi there,

Hope you are all well, it's been a while.

As always I've had a scratch about but I can't seem to find an answer to this.

I've got a formula

=XLOOKUP($A2&"ebrf",External_survey_block_attribute!$C$1:$C$12135&External_survey_block_attribute!$G$1:$G$12135,External_survey_block_attribute!$K$1:$K$12135,"",0,1)

I've got a data dump of 70,000 lines and I'm trying to extract the relevant data that I need from it.

I discovered the two criteria xlookup which is just brilliant. Most of the data only has 1 relevant line to return to the lookup, all so far so good.

However, due to historic issues with the data, sometimes there's a second line which matches the lookup so I wondered if there was a way to change the formula to skip the first lookup, and return the data from the second instance?

Does that make sense?

Appreciate any pointers that anyone can give.

 

[Cubist]

Try changing the search mode to -1.

 

[ECB]

Great thanks, I'll try that.

What if there was a third instance and I wanted to find that?

Sorry, just to elaborate:

The problem with -1 is that it returns the only line if there is only a single line. I'm looking for something that will return both lines?

 

[Cubist]

Search mode -1 looks for the last instance, so if the third instance is also the last then that should work.

Edit: Try FILTER.

=FILTER(External_survey_block_attribute!$K$1:$K$12135,External_survey_block_attribute!$C$1:$C$12135&External_survey_block_attribute!$G$1:$G$12135=$A2&"ebrf)