VLookUp/ Index+Match to find SECOND instance

Question

VLookUp/ Index+Match to find SECOND instance

MarkBeck (TechnicalUser)

(OP)

7 Jan 08 16:05

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.

Any ideas? Should I go VBA?

Thanks

Mark

SkipVought (Programmer) · Answer 1 · 2008-01-07 16:09:28

Hi,

You're going to have to use the Find method in VBA. Please repost in forum707: VBA Visual Basic for Applications (Microsoft).

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

Fenrirshowl (TechnicalUser) · Answer 2 · 2008-01-08 03:22:19

If the data in a single column and you either know the end point or can search to the end of the sheet (I prefer to hardcode a fixed point) you can then use the match and indirect functions to find them.

Assume column A holds single characters and you want to find all the "a"s.  The range is A1:A20.  The "a"s are in rows 4, 10 and 19.

First use the MATCH function to find the first occurance.
=MATCH("a",A1:A20,0)
I assume this is in B1, it returns 4

Next use that result in the next function:
=MATCH("a",INDIRECT("A" & B1+1 & ":$A$20"),0)+B1
The match function returns 6, but then you add on 4 to give 10.

Then you can drag this down to complete the rest of the searches.

If you don't have a fixed end point or want to make it more general you can find the last line of the data (maybe with a COUNTA function or something if no entries are blank) and then use that in the second part of the INDIRECT functions.  The first MATCH can also use the INDIRECT function and reference the last row.
i.e. B2 could be replaced with:
=MATCH("a",INDIRECT("A"&B1+1&":A" & COUNTA(A:A)),0)+B1

To keep the spreadsheet looking neat (rather than having error msgs if you have more functions that occurances) you can use a COUNTIF to find the number of occurances and then put the MATCH and INDIRECT functions into IF statements such that if the number of IF statements above the current cell is equal to or greater than the number of occurances then the cell returns a blank - you could always reference the row number of the cell in question rather than counting the cells above.
i.e. If C1 contains =COUNTIF(A:A,"a")
(which equals 3)
You can amend the MATCH function in B2 to:
=IF(ROW()>$C$1,"",MATCH("a",INDIRECT("A"&B1+1&":A"&COUNTA(A:A)),0)+B1)

Hope this is what you were looking for.

Fen

GlennUK (MIS) · Answer 3 · 2008-01-08 05:13:58

Use OFFSET to generate a secondary search area, starting after the first MATCH.

E.g.

CODE

=MATCH("mystring",OFFSET(A1,MATCH("mystring",A1:A999,0),0,999,1),0)+MATCH("mystring",A1:A999,0)

gives the second position of "mystring" ... which can be fed into an INDEX as required.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

MJPPaba (MIS) · Answer 4 · 2008-01-08 07:45:31

CODE

ColA ColB        ColC            ColD
a    1    =COUNTIF($A$2:A2,A2)        =+A2&C2
b    2    =COUNTIF($A$2:A3,A3)        =+A3&C3
c    3    =COUNTIF($A$2:A4,A4)        =+A4&C4
d    4    =COUNTIF($A$2:A5,A5)        =+A5&C5
e    5    =COUNTIF($A$2:A6,A6)        =+A6&C6
f    6    =COUNTIF($A$2:A7,A7)        =+A7&C7
g    7    =COUNTIF($A$2:A8,A8)        =+A8&C8
h    8    =COUNTIF($A$2:A9,A9)        =+A9&C9
a    9    =COUNTIF($A$2:A10,A10)    =+A10&C10
b    10    =COUNTIF($A$2:A11,A11)    =+A11&C11
c    11    =COUNTIF($A$2:A12,A12)    =+A12&C12
d    12    =COUNTIF($A$2:A13,A13)    =+A13&C13
e    13    =COUNTIF($A$2:A14,A14)    =+A14&C14
f    14    =COUNTIF($A$2:A15,A15)    =+A15&C15
g    15    =COUNTIF($A$2:A16,A16)    =+A16&C16
a    16    =COUNTIF($A$2:A17,A17)    =+A17&C17
b    17    =COUNTIF($A$2:A18,A18)    =+A18&C18
c    18    =COUNTIF($A$2:A19,A19)    =+A19&C19
d    19    =COUNTIF($A$2:A20,A20)    =+A20&C20
e    20    =COUNTIF($A$2:A21,A21)    =+A21&C21
f    21    =COUNTIF($A$2:A22,A22)    =+A22&C22
g    22    =COUNTIF($A$2:A23,A23)    =+A23&C23

        lookup value =     a

        Number of instances =    =COUNTIF($A:$A,D26)

        Instance to return value =    2

See if that works for you

Cheese

Matt

Rofeu (TechnicalUser) · Answer 5 · 2008-01-09 09:32:55

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):

CODE

={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

ps posted this in the other thread as well

MarkBeck (TechnicalUser) · Answer 6 · 2008-01-09 10:30:45

Thank you everyone! I am working on BOTH the excel and VBA solutions.

Come Join Us!

Posting Guidelines

Jobs

VLookUp/ Index+Match to find SECOND instance