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)

 

[ECB]

Hi all,

hope you are well, I've just about got a working formula, which just about does what I need:

=IFERROR(INDEX($B$2:$B$14,SMALL(IF($A$2:$A$14=$D2,ROW($A$2:$A$14)-1,""),COLUMNS($E$1:E1))),"")

Which when copied across columns does the trick on a single lookup.

However, I'm wanting to lookup on two columns, is that possible?

The bit I need to change I think

IF($A$2:$A$14=$D2

Can that be changed to something like

IF($A$2:$A$14=$D2 AND IF($b$2:$b$14=$c2), etc etc

Does that make sense? Is it even possible?