Solved =INDEX how do I get the next in range

DerbyDad03 February 7, 2013 at 09:00:22

✔ Best Answer

It's not clear to me where you want the results to be placed. When you say "How do I get it to return all the numbers in col A where there is a match" it sounds like you want a single formula to return multiple values. That won't work. I'll assume you want the first value for the first match to be in B2, the next in B3, etc. As you probably already know, your formula is only going to return the first MATCH it finds. Therefore, we need to change what it is looking for by creating unique strings for each of the values in BY2:BY12444. To do that we'll use a Helper Column. Let's say you have this: BY 1 2 Tom 3 Fred 4 Sue 5 Fred In BX2 enter this formula and drag it down to BX12444 =BY2&" "&COUNTIF($BY$2:$BY2,BY2) You should now have this: BX BY 1 2 Tom 1 Tom 3 Fred 1 Fred 4 Sue 1 Sue 5 Fred 2 Fred We now have unique values for the MATCH function to find. Now, in B2 place this modified version of your formula and drag it down. =INDEX(Data!$A$2:$A$12444,MATCH($I$2&" "&ROW()-1,Data!$BX$2:$BX$12444,0)) The ROW() function simply returns the number of the Row it is placed in, so =ROW()-1 will return 1 in Row 2, 2 in Row 3, etc. Note that we are now looking for a MATCH in Column BX and what we are matching is Tom 1, Tom 2, etc. not just Tom. Therefore you'll get every match for the values in Column BY. Once you drag it down past the number of matches, it will return #N/A, so wrap the formula in an IF(ISNA(MATCH(...) function and you'll be good to go. =IF(ISNA(MATCH($I$2&" "&ROW()-1,Data!$BX$2:$BX$12444,0)),"",INDEX(Data!$A$2:$A$12444,MATCH($I$2&" "&ROW()-1,Data!$BX$2:$BX$12444,0))) Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

emory February 8, 2013 at 03:31:51

Re Question =INDEX How Do I Get The Next In Range Hi DerbyDad03 This was a perfect score, if there are 5 postcodes the same held on the database it numbers them 1-5 or two post codes the same ZZ007 AAA 1, ZZ007 AAA 2 =BY2&" "&COUNTIF($BY$2:$BY2,BY2) I can use this elsewhere so this was very useful tool. The second part of the puzzel I have studied the formula and I don’t think I have explained this correctly The first section gives each post code a unique number I have a list 12,000 post codes held in Col CA, I have converted those as you suggested into Col BZ - ZZ1 1AA 1 ZZ1 1AA 2 etc. I have a list of 1000 post codes in ,COL BX (some are duplicates which I can data sort out) I need to find where the ZZ1 1AA are in the database according to Col A which is numbered 1-12,244 "=IF(ISNA(MATCH($BX$2&" "&ROW()-1,$BZ$2:$BZ$12444,0)),"",INDEX(Data!$A$2:$A$12444,MATCH($BX$2&" "&ROW()-1,$BZ$2:$BZ$12444,0))) Cols BX and BY look like this ZZ1 1AA 2520 AA1 ZZ1 2521 ZZ1 1AA falls in ROWS 2520 and 2520 Many Thanks

DerbyDad03 February 8, 2013 at 07:34:15

It's all working fine for me, based on what I think your spreadsheet looks like. I copied the formulas directly from your post and had no problem pulling multiple values from Column A based on the number of matches in Column BY. For ease of setup I put 623 sets of 20 values in Column BY. When I put a value in I2 that matches one of the 20 values from BY, I get 623 values from Column A in N2:N624. I assume you are aware that the value in I2 needs to be a match to the values in BY. The INDEX(MATCH...) formula will add the Row numbers to that value as you drag it down, creating the unique to serch Column M for. Did you drag the formula in Column N down as far as you think it will ever need to go? For example, you have 12443 records to search. Theoretically, you could have 12443 matches (if all 12444 records were the same) so you need to drag the formula down from N2 to N12444. Obviously, if you know the top limit of matches, e.g. 200, you would only need to drag it down 200 rows. If dragging the formula down or not putting the correct value in I2 is not the problem, there's not much more help I can be without knowing exactly how your spreadsheet is set up. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

emory February 8, 2013 at 08:23:35

Hi You are right it does work, but is wasn’t appearing how I expecting. I have studied it for ages and the only way I get the result is to transpose the 1000 postcodes into ROW 1 then I get the following: Post Code Post Code Post Code Post Code Post Code ZZ1 AA1 Post Code 3798 1639 2821 3120 3070 3103 3124 1640 3905 3121 3104 3125 3556 3105 3997 3106 3107 3108 3109 3110 3601 3650 3911 In the data 0f 12,000 the post codes appear in the line numbers listed above, as you can see ZZ1 AA1 appears 11 times in the list with the corresponding reference number from COL A. The problem with this method as you can is that I don’t think you can go 1000 columns across and "=IF(ISNA(MATCH($S$1&" "&ROW()-1,$M$2:$M$12444,0)),"",INDEX(Data!$A$2:$A$12444,MATCH($S$1&" "&ROW()-1,$M$2:$M$12444,0))) you have to manually change the column letter in the formula Any ideas would be gratefully appreciated Thanks

DerbyDad03 February 8, 2013 at 08:51:01

Sorry, but your last post is extremely confusing. You said: "In the data 0f 12,000 the post codes appear in the line numbers listed above, as you can see ZZ1 AA1 appears 11 times in the list with the corresponding reference number from COL A." I only see ZZ1 AA1 once. I also don't know what you mean by "the line numbers listed above". What line numbers? Then you said: The problem with this method as you can is that I don’t think you can go 1000 columns across and "=IF(ISNA(MATCH($S$1&" "&ROW()-1,$M$2:$M$12444,0)),"",INDEX(Data!$A$2:$A$12444,MATCH($S$1&" "&ROW()-1,$M$2:$M$12444,0)))" That "sentence" doesn't make sense from a grammatical standpoint. I have no idea what you are trying to say. In addition, I have no idea why you are going across columns. Do you not have a layout similar to what I posted in Response #1? Are you not trying to get all of the INDEXED values from Column A to appear in Column N? Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

emory February 8, 2013 at 09:17:59

Re Question =INDEX How Do I Get The Next In Range Hi DerbyDad03 You are right the cols are all out and its goobly gook Iv'e been at this computer too long today I will have a fresh look Saturday Thanks for you help

emory February 9, 2013 at 04:30:21

Hi DerbyDad03 I've have a fresh look at it with made up data and these are the results I get I am not sure what I am doing wrong PPP AAA ZZZ AAQ PPP AAB ZZZ AAX ZZZ AAA ZZZ AAP 1 1 ZZZ AAA ZZZ AAA 1 ZZZ AAA 13 5 2 6 1 7 2 9 PPP AAA PPP AAB 1 PPP AAB 14 21 10 22 9 3 PPP AAA PPP AAC 1 PPP AAC 15 23 4 PPP AAA PPP AAS 1 PPP AAS 16 24 5 ZZZ AAQ ZZZ AAQ 1 ZZZ AAQ 17 25 6 ZZZ AAX ZZZ AAX 1 ZZZ AAX 18 26 7 ZZZ AAP ZZZ AAP 1 ZZZ AAP 19 8 ZZZ AAZ ZZZ AAZ 1 ZZZ AAZ 20 9 ZZZ AAA ZZZ AAA 2 ZZZ AAA 10 PPP AAB PPP AAB 2 PPP AAB 11 PPP AAC PPP AAC 2 PPP AAC 12 PPP AAS PPP AAS 2 PPP AAS 13 ZZZ AAA PPP AAA 1 PPP AAA 14 PPP AAA PPP AAA 2 PPP AAA 15 PPP AAA PPP AAA 3 PPP AAA 16 PPP AAA PPP AAA 4 PPP AAA 17 ZZZ AAQ PPP AAA 5 PPP AAA 18 ZZZ AAX PPP AAA 6 PPP AAA 19 ZZZ AAP PPP AAA 7 PPP AAA 20 ZZZ AAZ PPP AAA 8 PPP AAA 21 ZZZ AAA ZZZ AAQ 2 ZZZ AAQ 22 PPP AAB ZZZ AAX 2 ZZZ AAX 23 PPP AAC ZZZ AAQ 3 ZZZ AAQ 24 PPP AAS ZZZ AAQ 4 ZZZ AAQ 25 PPP AAC ZZZ AAQ 5 ZZZ AAQ 26 PPP AAS ZZZ AAQ 6 ZZZ AAQ Thanks

emory February 9, 2013 at 04:32:22

The two formulas I am using are In Col B "=IF(ISNA(MATCH($C$2&" "&ROW()-1,$D$2:$D$12444,0)),"",INDEX($A$2:$A$12444,MATCH($C$2&" "&ROW()-1,$D$2:$D$12444,0))) In Col F1 "=IF(ISNA(MATCH($F$1&" "&ROW()-1,$D$2:$D$12444,0)),"",INDEX($A$2:$A$12444,MATCH($F$1&" "&ROW()-1,$D$2:$D$12444,0)))

DerbyDad03 February 9, 2013 at 05:43:47

It's your job to make my job as easy as possible. I don't see any column letters in your post and I don't feel like trying to figure/guess what your spreadsheet layout is. I also don't know what all the columns with the numbers on the right side are for. There is no mention of them in your requirements. All you asked for was how to Match multiple values using INDEX(MATCH(...) Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

emory February 9, 2013 at 06:22:46

Sorry you didnt get enough info I thought you would like see that for some reason this doesn't work many thanks for your help 1 A B C D E F G H I J K 2 PPP AAA ZZZ AAQ PPP AAB ZZZ AAX ZZZ AAA ZZZ AAP 3 1 9 ZZZ AAA ZZZ AAA 1 ZZZ AAA 14 21 10 22 9 4 2 PPP AAA PPP AAB 1 PPP AAB 15 23 5 3 PPP AAA PPP AAC 1 PPP AAC 16 24 6 4 PPP AAA PPP AAS 1 PPP AAS 17 25 7 5 ZZZ AAQ ZZZ AAQ 1 ZZZ AAQ 18 26 8 6 ZZZ AAX ZZZ AAX 1 ZZZ AAX 19 9 7 ZZZ AAP ZZZ AAP 1 ZZZ AAP 20 10 8 ZZZ AAZ ZZZ AAZ 1 ZZZ AAZ 11 9 ZZZ AAA ZZZ AAA 2 ZZZ AAA 12 10 PPP AAB PPP AAB 2 PPP AAB 13 11 PPP AAC PPP AAC 2 PPP AAC These Columns work going down With this formula "=IF(ISNA(MATCH($G$2&" "&ROW()-1,$E$3:$E$12445,0)),"",INDEX($B$3:$B$12445,MATCH($G$2&" "&ROW()-1,$E$3:$E$12445,0))) 14 12 For some reason in wont work going down Col B with "=IF(ISNA(MATCH($D$3&" "&ROW()-1,$E$3:$E$12445,0)),"",INDEX($B$3:$B$12445,MATCH($D$3&" "&ROW()-1,$E$3:$E$12445,0))) PPP AAS PPP AAS 2 PPP AAS 15 13 ZZZ AAA PPP AAA 1 PPP AAA 16 14 PPP AAA PPP AAA 2 PPP AAA 17 15 PPP AAA PPP AAA 3 PPP AAA 18 16 PPP AAA PPP AAA 4 PPP AAA 19 17 ZZZ AAQ PPP AAA 5 PPP AAA 20 18 ZZZ AAX PPP AAA 6 PPP AAA 21 19 ZZZ AAP PPP AAA 7 PPP AAA 22 20 ZZZ AAZ PPP AAA 8 PPP AAA 23 21 ZZZ AAA ZZZ AAQ 2 ZZZ AAQ 24 22 PPP AAB ZZZ AAX 2 ZZZ AAX 25 23 PPP AAC ZZZ AAQ 3 ZZZ AAQ 26 24 PPP AAS ZZZ AAQ 4 ZZZ AAQ 27 25 PPP AAC ZZZ AAQ 5 ZZZ AAQ 28 26 PPP AAS ZZZ AAQ 6 ZZZ AAQ

DerbyDad03 February 9, 2013 at 12:36:49

I seriously doubt your spreadsheet is set up as you've shown. Do you really have Column letters in Row 1? I also can't tell what is going on with Columns C-D or F, G, etc. I still can't tell what data is in which column. Perhaps you should use some simpler data, such a single words like I did. It's much easier to get them to line up under a column letter. All of your multiple groupings of three letters is just adding confusion to the issue. Once we get this working the way you want with simple pieces of data, the concept should work with any data. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.