VLookup 2nd 3rd & 4th occurrence.
Author 
Message 
Sh0t2bt #1 / 6

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 


Harlan Grov #2 / 6

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 


Mark Willet #3 / 6

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 


Mark Willet #4 / 6

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 


Harlan Grov #5 / 6

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 


Sh0t2bt #6 / 6

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 


