Seeking MATCH and INDEX Assistance

[kstehly]

I am utilizing a combined MATCH and INDEX function to search several columns of data and return data in a specific column from the MATCHed ROW. It works when I only search 1 column, however when I ask it to search multiple columns or an array, it returns a N/A. Any idea why it won't search the array / multiple columns?

Alternately, is there a function which combines multiple searches so I can search multiple columns?

Any assistance would be greatly appreciated. Thanks.

 

[jeffreybrown]

Hello & Welcome to the board,

Maybe this will help...

http://www.contextures.com/xlfunctions03.html

If you have something specific, look at my signature and use one of the HTML to post a small sample screen shot with a desired result.

 

[kstehly]

Hi Jeff -

Thanks for the helpful link. My issue might be that I have letters, numbers, and combined data.

Here's a sample of the calculations / formulas:

Excel Workbook
	A	B	C	D	E	F
1		SEWER SERVICES TABULATIONS
2		PERMIT NO.** - ORIGINAL COLUMN	1 PERMIT NO.**	1 ADDRESS	2 PERMIT NO.**	3 PERMIT NO.**
3
4
5
6		A69563, R111318	69563	3415	R111318
7		A22839	22839	2675
8		A22839	22839	2675
9		A17271	A17271	#N/A
10		RA5935, A54673	RA5935	#N/A	A54673
11		RA5935, RA16390, A54283	RA5935		RA16390	A54283
12		A100562	A100562
13		RA16390, A81532	RA16390		A81532
14		RA16390, A38673	RA16390		A38673
15		A100904, RA16108, A65557	A100904		RA16108	A65557
16		A100906	A100906
17		RA16103, A89218	RA16103		A89218
18		RA16104, R9159, A24578	RA16104		R9159	A24578
19		A52297	A52297
20		A15997	A15997
21		A92572	A92572
22		A63941, R1451, A17909	A63941		R1451	A17909
23		A60411	A60411
24		A58138	A58138
25		A58138	A58138
26		A16653	A16653
27		RA16682, A33944	RA16682		A33944
28		RA13034, A67037	RA13034		A67037
29		A21114	A21114
Working

Excel 2007

Cell Formulas
Range	Formula
D6	=INDEX('Segments 2, 3 & 4'!B:B,MATCH($C6,'Segments 2, 3 & 4'!D:D,FALSE),1)
D7	=INDEX('Segments 2, 3 & 4'!B:B,MATCH($C7,'Segments 2, 3 & 4'!D:D,FALSE),1)
D8	=INDEX('Segments 2, 3 & 4'!B:B,MATCH($C8,'Segments 2, 3 & 4'!D:D,FALSE),1)
D9	=INDEX('Segments 2, 3 & 4'!B:B,MATCH($C9,'Segments 2, 3 & 4'!D:D,FALSE),1)
D10	=INDEX('Segments 2, 3 & 4'!B:B,MATCH($C10,'Segments 2, 3 & 4'!D:D,FALSE),1)

And here's the data it's pulling from:

Excel Workbook
	A	B	C	D	E	F	G	H	I
22		Segment 2: University Ave. - Emerald St. to Eustis St.
23		Address	Ramsey Co.	Sanitary Service 1	Sanitary Service 2	Sanitary Service 3	Sanitary Service 4	Sanitary Service 5	Active Permits
24		3415	3415	69563	R-111318
25	X	2701
26	X	2701-AF
27		2700	2700						39989
28		2686
29		2685-2695
30		2675	2665	22839
31		2675-AF
32		2674
33		2673
34		2665
35		2640-2662
36		2650	2650	111147
37		2642	2642	58483
38		2635	2635	100567					17271
39		2635-AF
40		2634
41		2610	2610	73065
42		2575	2575	100904					R-9159
43		2575-AF
44		2550	2550	R-15055					32732
45									33007
46
47		Segment 2: University Ave. - Cromwell Ave. to Raymond Ave.
48		Address	Ramsey Co.	Sanitary Service					Active Permits
49		2505	2505	52297	52296
50		2504
51		2486	2512	34277
Segments 2, 3 & 4

Excel 2007



If there isn't any way around this, can I set up in one cell, sequential INDEX / MATCH functions that lookup data by column? Do you know the function for this operation?

Thanks,
Kristine

 

[jeffreybrown]

Hi Kristine,

Not sure what you are asking here? Could you elaborate and what is not working? BTW, I think the formula should just look like...the 1 on the end is not neccesary.

=INDEX('Segments 2, 3 & 4'!B:B,MATCH($C6,'Segments 2, 3 & 4'!D:D,0))

 

[kstehly]

Thanks Jeff. I think I have come to the conclusion that MATCH will not search an ARRAY with multiple columns - - - do you agree?

Is there another function you are aware of which will?

 

[jeffreybrown]

It's hard for me to agree or disagree when I'm not sure of what you are seeking.

You can use index/match to search in a grid pattern. So lets say down column A you are searching for March and then in row1 you are searching for Payment.

This would look like with B2:F13 the grid in which you are searching for a value.

I believe the link I posted goes over this

=INDEX($B$2:$F$13,MATCH("March",$A$2:$A$13,0),MATCH("Payment",$B$1:$F$1,0))

Can you somehow post a sample of what you would like?

 

[kstehly]

Hi Jeff -

I'm asking for Excel to search Columns D through I, and return the value in Column A in the same row for the Second Screenshot - Segments 2, 3 & 4.

It keeps giving me a #N/A when I attempt to use an array, and I don't want to set up repetitve formulas for the multiple columns of data I need to search.

Thanks for your patience.

 

[jeffreybrown]

Hi Kristine,

Not quite sure on this one. Hopefully somebody will help out.

 

[Haseeb Avarakkan]

Try;

Array Formula, Confirmed with CTRL+SHIFT+ENTER, not just ENTER.

=INDEX('Segments 2, 3 & 4'!$B:$B,SMALL(IF('Segments 2, 3 & 4'!$C$3:$I$100=$C6,ROW('Segments 2, 3 & 4'!$C$3:$I$100)),1))

Note: C6 value is not in some where in 'Segments 2, 3 & 4'!$C$3:$I$100, this will give you error.

 

[jeffreybrown]

Thank you Haseeb.

On the test data I have setup this works great.