Index+Match to find SECOND instance

[MarkBeck]

Hi

I have a long data sheet. I am using a Index+Match formula to retrieve the first instance of a given criteria. I also need to retrieve the SECOND instance belonging to the given criteria.

I cant use an OFFSET, since they next value is not necessarily the very next line.

How do i do this? One option is to COUNT each instance and attach a number at the end, (concatenate(a50,countif(A$2:A50,A50)), but this is difficult here.

I was advised over at the Office forum to go VBA.

Thanks


Mark

 

[SkipVought]

Use the Find method. Test the row and column properties of the found cell to determine if you have wrapped the search.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Rofeu]

Hi,

you don't need to go VBA for this. It can be done through regular Excel formulae.

Use the following array-formula (enter with cntrl-shift-enter):

={OFFSET($B$1,SMALL(IF($A$1:$A$6=$D$2,ROW($A$1:$A$6),""),$E2)-1,0)}

with
$B$1 = the first cell of the column with return-values
$A$1:$A$6 = Lookup_Array
$D$2 = Lookup_Value
$E$2 = Instance to be returned

Cheers,

Roel

 

[MarkBeck]

Thanks Skip and Rofeu.

I might use the excel solution for now, but am working on VBA code for (probably) a speedier solution, with the data being 10k+ in length.
One question; I never did an Array formula. what is the benefit? and how is it different to regular?

 

[AnotherHiggins]

Notice these bits:
...IF([!]$A$1:$A$6=$D$2[/!]...
...ROW([!]$A$1:$A$6[/!])...

An Array Formula loops through all those ranges to check each individual cell.

Note: If you are going to copy and paste Roel's (impressive) formula, remove the curly brackets - [highlight]{[/highlight] and [highlight]}[/highlight] - before pressing [Ctrl]+[Shift]+[Enter]. Excel will place those around the formula to indicate that it is an Array Formula, but it won't accept it if you try to enter them yourself.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.