Solved =INDEX how do I get the next in range

February 7, 2013 at 06:54:09
Specs: Windows 7, 2.128 GHz / 1014 MB
Hi Can anybody help with this problem

"=INDEX(Data!$A$2:$A$12444,MATCH(I2,Data!$BY$2:$BY$12444,0))

This works fine, it returns the number in col A where it finds a match, but there is more than one match. Ho do I get it to return all the numbers in col A where there is a match

Many thanks


See More: =INDEX how do I get the next in range

Report •


#1
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.


Report •

#2
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


Report •

#3
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.


Report •

Related Solutions

#4
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


Report •

#5
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.


Report •

#6
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


Report •

#7
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


Report •

#8
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)))


Report •

#9
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.


Report •

#10
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						


Report •

#11
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.


Report •


Ask Question