VLookup 2nd 3rd & 4th occurrence. 
Author Message
 VLookup 2nd 3rd & 4th occurrence.

Hi All,

I am trying to match the first, second, third and fourth occurrence of a
time in using VLookup.

Here's what I have:-

   A    B     C
1 0800   10   0800
2 0830    10
3 0800    11
4 0830    11
5 0800    12
6 0830   12

The times in column A range from 0000 to 2330 and are repeated up to 4
times.
The data in column's B,C,D,Etc changes with each occurrence.

C1 = what I want to match
A1:B6 = My table

I have found this in this NG:-
=VLOOKUP($C$1,INDIRECT("A"&MATCH($C$1,A1:A6,0)+1&":B6"),2,FALSE)

Which match's the second occurrence, How do I adapt it to match the 3rd &
forth?
Or is there an easier way of doing this?

I do need to pull all of the data in the matched row from column B through
to column IH

Many Thanks for you help.

Mark :o)



Sun, 17 Apr 2005 02:25:40 GMT  
 VLookup 2nd 3rd & 4th occurrence.
Quote:

>I am trying to match the first, second, third and fourth occurrence of a
>time in using VLookup.

>Here's what I have:-

>   A    B     C
>1 0800   10   0800
>2 0830    10
>3 0800    11
>4 0830    11
>5 0800    12
>6 0830   12

>The times in column A range from 0000 to 2330 and are repeated up to 4
>times. The data in column's B,C,D,Etc changes with each occurrence.

>C1 = what I want to match
>A1:B6 = My table

>I have found this in this NG:-
>=VLOOKUP($C$1,INDIRECT("A"&MATCH($C$1,A1:A6,0)+1&":B6"),2,FALSE)

>Which match's the second occurrence, How do I adapt it to match the 3rd &
>forth?

...

The formula above can be generalized, but you're better off filtering rather
than looking up. Enter the following array formula in cell E1.

=INDEX(MyTable,SMALL(IF(INDEX(MyTable,0,1)=ValToBeMatched,
ROW(MyTable)-CELL("Row",MyTable)+1),ROW()-ROW($E$1)+1),2)

Fill E1 down until it evaluates #NUM!. If there are M matches of
ValToBeMatched in MyTable, then the Nth match (1 <= N <= M, from top to
bottom) is given by

=INDEX(MyTable,SMALL(IF(INDEX(MyTable,0,1)=ValToBeMatched,
ROW(MyTable)-CELL("Row",MyTable)+1),N),2)
--
Public Service Announcement:
Don't attach files to postings in this newsgroup.



Sun, 17 Apr 2005 03:25:52 GMT  
 VLookup 2nd 3rd & 4th occurrence.
Harlan

I can not get the array you advised of to work

As I could not get the array working I continued with the lookup as you said
"The formula above can be generalized" I though it could not be too hard,
Duh!

I have extended my table from A1:B6 to A1:B16, same data just more of it.

This is what I posted last night and finds the second match

=VLOOKUP($C$1,INDIRECT("A"&MATCH($C$1,A1:A16,0)+1&":B16"),2,FALSE)

So what I though would work is if where "A1" is in this part
MATCH($C$1,A1:A16,0), If I inserted another indirect match it would find the
second match add 1 to the row and the find the 3rd match.

=VLOOKUP($C$1,INDIRECT("A"&MATCH($C$1,INDIRECT("A"&MATCH($C$1,A1:A16,0)+1&":
B16"):A16,0)+1&":B16"),2,FALSE)

All I get back is #N/A

Could you shed any light on where I am going wrong or how you would do it.

If you have time would you mind sending a partial sheet where you have it
working, this will get rid of any typo's I create :o)

Many Thanks it is most appreciated.

Mark


Quote:

> >I am trying to match the first, second, third and fourth occurrence of a
> >time in using VLookup.

> >Here's what I have:-

> >   A    B     C
> >1 0800   10   0800
> >2 0830    10
> >3 0800    11
> >4 0830    11
> >5 0800    12
> >6 0830   12

> >The times in column A range from 0000 to 2330 and are repeated up to 4
> >times. The data in column's B,C,D,Etc changes with each occurrence.

> >C1 = what I want to match
> >A1:B6 = My table

> >I have found this in this NG:-
> >=VLOOKUP($C$1,INDIRECT("A"&MATCH($C$1,A1:A6,0)+1&":B6"),2,FALSE)

> >Which match's the second occurrence, How do I adapt it to match the 3rd &
> >forth?
> ...

> The formula above can be generalized, but you're better off filtering
rather
> than looking up. Enter the following array formula in cell E1.

> =INDEX(MyTable,SMALL(IF(INDEX(MyTable,0,1)=ValToBeMatched,
> ROW(MyTable)-CELL("Row",MyTable)+1),ROW()-ROW($E$1)+1),2)

> Fill E1 down until it evaluates #NUM!. If there are M matches of
> ValToBeMatched in MyTable, then the Nth match (1 <= N <= M, from top to
> bottom) is given by

> =INDEX(MyTable,SMALL(IF(INDEX(MyTable,0,1)=ValToBeMatched,
> ROW(MyTable)-CELL("Row",MyTable)+1),N),2)
> --
> Public Service Announcement:
> Don't attach files to postings in this newsgroup.



Mon, 18 Apr 2005 03:43:04 GMT  
 VLookup 2nd 3rd & 4th occurrence.
Harlan

I can not get the array you advised of to work

As I could not get the array working I continued with the lookup as you said
"The formula above can be generalized" I though it could not be too hard,
Duh!

I have extended my table from A1:B6 to A1:B16, same data just more of it.

This is what I posted last night and finds the second match

=VLOOKUP($C$1,INDIRECT("A"&MATCH($C$1,A1:A16,0)+1&":B16"),2,FALSE)

So what I though would work is if where "A1" is in this part
MATCH($C$1,A1:A16,0), If I inserted another indirect match it would find the
second match add 1 to the row and the find the 3rd match.

=VLOOKUP($C$1,INDIRECT("A"&MATCH($C$1,INDIRECT("A"&MATCH($C$1,A1:A16,0)+1&":
B16"):A16,0)+1&":B16"),2,FALSE)

All I get back is #N/A

Could you shed any light on where I am going wrong or how you would do it.

If you have time would you mind sending a partial sheet where you have it
working, this will get rid of any typo's I create :o)

Many Thanks it is most appreciated.

Mark


Quote:

> >I am trying to match the first, second, third and fourth occurrence of a
> >time in using VLookup.

> >Here's what I have:-

> >   A    B     C
> >1 0800   10   0800
> >2 0830    10
> >3 0800    11
> >4 0830    11
> >5 0800    12
> >6 0830   12

> >The times in column A range from 0000 to 2330 and are repeated up to 4
> >times. The data in column's B,C,D,Etc changes with each occurrence.

> >C1 = what I want to match
> >A1:B6 = My table

> >I have found this in this NG:-
> >=VLOOKUP($C$1,INDIRECT("A"&MATCH($C$1,A1:A6,0)+1&":B6"),2,FALSE)

> >Which match's the second occurrence, How do I adapt it to match the 3rd &
> >forth?
> ...

> The formula above can be generalized, but you're better off filtering
rather
> than looking up. Enter the following array formula in cell E1.

> =INDEX(MyTable,SMALL(IF(INDEX(MyTable,0,1)=ValToBeMatched,
> ROW(MyTable)-CELL("Row",MyTable)+1),ROW()-ROW($E$1)+1),2)

> Fill E1 down until it evaluates #NUM!. If there are M matches of
> ValToBeMatched in MyTable, then the Nth match (1 <= N <= M, from top to
> bottom) is given by

> =INDEX(MyTable,SMALL(IF(INDEX(MyTable,0,1)=ValToBeMatched,
> ROW(MyTable)-CELL("Row",MyTable)+1),N),2)
> --
> Public Service Announcement:
> Don't attach files to postings in this newsgroup.



Mon, 18 Apr 2005 03:43:31 GMT  
 VLookup 2nd 3rd & 4th occurrence.
...

Quote:
>I can not get the array you advised of to work

>As I could not get the array working I continued with the lookup as you
said
>"The formula above can be generalized" I though it could not be too hard,
>Duh!

Did you enter the array formula by typing in the formula, holding down
[Ctrl] and [Shift] keys then pressing the [Enter] key? What result were you
getting that didn't work?

Note: I tested my formula on the sample data in your original post, and it
works for me.

Quote:
>I have extended my table from A1:B6 to A1:B16, same data just more of it.

>This is what I posted last night and finds the second match

>=VLOOKUP($C$1,INDIRECT("A"&MATCH($C$1,A1:A16,0)+1&":B16"),2,FALSE)

>So what I though would work is if where "A1" is in this part
>MATCH($C$1,A1:A16,0), If I inserted another indirect match it would find
the
>second match add 1 to the row and the find the 3rd match.

...

The problem is that you'd need to do this recursively to find the 3rd and
subsequent matches. That is, the 3rd match would have to be found as

=VLOOKUP($C$1,
  INDIRECT("A"&
    MATCH($C$1,
      INDIRECT("A"&
        MATCH($C$1,A1:A16,0)+1
        &":A16"),0)+1
    &":B16"),
2,FALSE)

Your (reformatted) formula is

=VLOOKUP($C$1,
  INDIRECT("A"&
    MATCH($C$1,
      INDIRECT("A"&
        MATCH($C$1,A1:A16,0)+1
        &":B16"):A16,0)+1
    &":B16"),
2,FALSE)

The problem with it is the term

INDIRECT("A"&MATCH($C$1,A1:A16,0)+1&":B16"):A16

If the 1st match were in A3, the INDIRECT function would give the range
A4:B16, but the term as a whole would be A4:B16:A16, which is syntactically
valid but semantically meaningless. If you change this term to either the

INDIRECT("A"&MATCH($C$1,A1:A16,0)+1&":A16")

from my formula or

INDIRECT("A"&MATCH($C$1,A1:A16,0)+1):A16

you'd get the desired result.

While this may fix the problem finding the 3rd match, the 4th match requires
yet another level of INDIRECT(MATCH()) calls. This approach doesn't scale
well given Excel's limit of 7 nested function calls. The filter approach I
suggested before does scale reasonably well, but it *MUST* be entered as an
array formula.
--
Public Service Announcement:
Don't attach files to postings in this newsgroup.



Mon, 18 Apr 2005 07:48:04 GMT  
 VLookup 2nd 3rd & 4th occurrence.
HI Again :o)

I am sure I entered it as an array "crtl + shift & enter"

I will try the array again today, "checking for typo's",

Another idea I have had is, as there is going to do around 100,000 formula's
in this sheet, +-a few, I could get a macro to workout where the next match
is and then get the macro to write the formula.. This would keep the size of
the workbook small and make changes easier to implement.

This is my second option if I can not get the array to work.

Thanks

Mark :o)

Quote:

> ...
> >I can not get the array you advised of to work

> >As I could not get the array working I continued with the lookup as you
> said
> >"The formula above can be generalized" I though it could not be too hard,
> >Duh!

> Did you enter the array formula by typing in the formula, holding down
> [Ctrl] and [Shift] keys then pressing the [Enter] key? What result were
you
> getting that didn't work?

> Note: I tested my formula on the sample data in your original post, and it
> works for me.

> >I have extended my table from A1:B6 to A1:B16, same data just more of it.

> >This is what I posted last night and finds the second match

> >=VLOOKUP($C$1,INDIRECT("A"&MATCH($C$1,A1:A16,0)+1&":B16"),2,FALSE)

> >So what I though would work is if where "A1" is in this part
> >MATCH($C$1,A1:A16,0), If I inserted another indirect match it would find
> the
> >second match add 1 to the row and the find the 3rd match.
> ...

> The problem is that you'd need to do this recursively to find the 3rd and
> subsequent matches. That is, the 3rd match would have to be found as

> =VLOOKUP($C$1,
>   INDIRECT("A"&
>     MATCH($C$1,
>       INDIRECT("A"&
>         MATCH($C$1,A1:A16,0)+1
>         &":A16"),0)+1
>     &":B16"),
> 2,FALSE)

> Your (reformatted) formula is

> =VLOOKUP($C$1,
>   INDIRECT("A"&
>     MATCH($C$1,
>       INDIRECT("A"&
>         MATCH($C$1,A1:A16,0)+1
>         &":B16"):A16,0)+1
>     &":B16"),
> 2,FALSE)

> The problem with it is the term

> INDIRECT("A"&MATCH($C$1,A1:A16,0)+1&":B16"):A16

> If the 1st match were in A3, the INDIRECT function would give the range
> A4:B16, but the term as a whole would be A4:B16:A16, which is
syntactically
> valid but semantically meaningless. If you change this term to either the

> INDIRECT("A"&MATCH($C$1,A1:A16,0)+1&":A16")

> from my formula or

> INDIRECT("A"&MATCH($C$1,A1:A16,0)+1):A16

> you'd get the desired result.

> While this may fix the problem finding the 3rd match, the 4th match
requires
> yet another level of INDIRECT(MATCH()) calls. This approach doesn't scale
> well given Excel's limit of 7 nested function calls. The filter approach I
> suggested before does scale reasonably well, but it *MUST* be entered as
an
> array formula.
> --
> Public Service Announcement:
> Don't attach files to postings in this newsgroup.



Mon, 18 Apr 2005 16:19:24 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Return 1st,2nd,3rd,4th from Date

2. Postions - 1st 2nd 3rd 4th etc

3. Microsof publisher How to print 2nd 3rd and 4th quarter of page

4. Defferences between 3rd and 4th generation language

5. VLOOKUP's 4th argument

6. Replace 2nd occurrence of a value

7. how to: subtracting 4th field in file1.csv from 4th field file2.csv

8. Resolve to 2nd or 3rd email

9. Get 1st, 2nd, and 3rd Letter From a String

10. OFFSET with array 2nd or 3rd argument


 
Powered by phpBB® Forum Software © phpBB Group