MATCH Worksheet Function

Introductory Remarks   (#intro)

TABLE -- Sheet BusType

	A	B	C	D	E	F	G	H	I
1	A13	B13-1	C13-1	D13-1	E13-1	F13-1	G13-1	H13-1	0
2	A14	B14-1	C14-1	D14-1	E14-1	F14-1	G14-1	H14-1	2
3	A1	B1-1	C1-1	D1-1	E1-1	F1-1	G1-1	H1-1	9
4	A4*	B4-1	C4-1	D4-1	E4-1	F4-1	G4-1	H4-1	11
5	A11	B11-1	C11-1	D11-1	E11-1	F11-1	G11-1	H11-1	14
6	A7	B7-1	C7-1	D7-1	E7-1	F7-1	G7-1	H7-1	16
7	A12	B12-1	C12-1	D12-1	E12-1	F12-1	G12-1	H12-1	40
8	A10	B10-1	C10-1	D10-1	E10-1	F10-1	G10-1	H10-1	41
9	A5	B5-1	C5-1	D5-1	E5-1	F5-1	G5-1	H5-1	43
10	A6	B6-1	C6-1	D6-1	E6-1	F6-1	G6-1	H6-1	55
11	A3	B3-1	C3-1	D3-1	E3-1	F3-1	G3-1	H3-1	56
12	A2	B2-1	C2-1	D2-1	E2-1	F2-1	G2-1	H2-1	78
13	A8	B8-1	C8-1	D8-1	E8-1	F8-1	G8-1	H8-1	85
14	A9	B9-1	C9-1	D9-1	E9-1	F9-1	G9-1	H9-1	88

Solution using INDEX and MATCH

	A2	B2	C2	D2	E2	F2	G2
22	a1	B1-1	C1-1	D1-1	E1-1	F1-1	B22: =INDEX(BusType!B:B,MATCH($A22,BusType!$A:$A,0))
23	a2	B2-1	C2-1	D2-1	E2-1	F2-1	B23: =INDEX(BusType!B:B,MATCH($A23,BusType!$A:$A,0))
24	a3	B3-1	C3-1	D3-1	E3-1	F3-1	B24: =INDEX(BusType!B:B,MATCH($A24,BusType!$A:$A,0))
25	a4	#N/A	#N/A	#N/A	#N/A	#N/A	B25: =INDEX(BusType!B:B,MATCH($A25,BusType!$A:$A,0))
26	a5	B5-1	C5-1	D5-1	E5-1	F5-1	B26: =INDEX(BusType!B:B,MATCH($A26,BusType!$A:$A,0))
27	a6	B6-1	C6-1	D6-1	E6-1	F6-1	B27: =INDEX(BusType!B:B,MATCH($A27,BusType!$A:$A,0))
28	a7	B7-1	C7-1	D7-1	E7-1	F7-1	B28: =INDEX(BusType!B:B,MATCH($A28,BusType!$A:$A,0))
29	a8	B8-1	C8-1	D8-1	E8-1	F8-1	B29: =INDEX(BusType!B:B,MATCH($A29,BusType!$A:$A,0))
30	a9	B9-1	C9-1	D9-1	E9-1	F9-1	B30: =INDEX(BusType!B:B,MATCH($A30,BusType!$A:$A,0))

Solution using VLOOKUP

Syntax:    (As always look in HELP for more information)
   VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
    range_lookup can be TRUE or FALSE, if omitted the default is TRUE meaning the list would be in collating sequence (sorted)

	A	B	C	D	E	F	G
1	A1	B1-1	C1-1	D1-1	E1-1	F1-1	B1: =VLOOKUP($A1,BusType!$A$1:$F$200,COLUMN(B1),0)
2	A2	B2-1	C2-1	D2-1	E2-1	F2-1	B2: =VLOOKUP($A2,BusType!$A$1:$F$200,COLUMN(B2),0)
3	A3	B3-1	C3-1	D3-1	E3-1	F3-1	B3: =VLOOKUP($A3,BusType!$A$1:$F$200,COLUMN(B3),0)
4	A4	#N/A	#N/A	#N/A	#N/A	#N/A	B4: =VLOOKUP($A4,BusType!$A$1:$F$200,COLUMN(B4),0)
5	A5	B5-1	C5-1	D5-1	E5-1	F5-1	B5: =VLOOKUP($A5,BusType!$A$1:$F$200,COLUMN(B5),0)
6	A6	B6-1	C6-1	D6-1	E6-1	F6-1	B6: =VLOOKUP($A6,BusType!$A$1:$F$200,COLUMN(B6),0)
7	A7	B7-1	C7-1	D7-1	E7-1	F7-1	B7: =VLOOKUP($A7,BusType!$A$1:$F$200,COLUMN(B7),0)
8	A8	B8-1	C8-1	D8-1	E8-1	F8-1	B8: =VLOOKUP($A8,BusType!$A$1:$F$200,COLUMN(B8),0)
9	A9	B9-1	C9-1	D9-1	E9-1	F9-1	B9: =VLOOKUP($A9,BusType!$A$1:$F$200,COLUMN(B9),0)
10	A10	B10-1	C10-1	D10-1	E10-1	F10-1	B10: =VLOOKUP($A10,BusType!$A$1:$F$200,COLUMN(B10),0)
11	A11	B11-1	C11-1	D11-1	E11-1	F11-1	B11: =VLOOKUP($A11,BusType!$A$1:$F$200,COLUMN(B11),0)
12	A12	B12-1	C12-1	D12-1	E12-1	F12-1	B12: =VLOOKUP($A12,BusType!$A$1:$F$200,COLUMN(B12),0)
13	A13	B13-1	C13-1	D13-1	E13-1	F13-1	B13: =VLOOKUP($A13,BusType!$A$1:$F$200,COLUMN(B13),0)
14	A14	B14-1	C14-1	D14-1	E14-1	F14-1	B14: =VLOOKUP($A14,BusType!$A$1:$F$200,COLUMN(B14),0)
15	A15	#N/A	#N/A	#N/A	#N/A	#N/A	B15: =VLOOKUP($A15,BusType!$A$1:$F$200,COLUMN(B15),0)
16	A16	#N/A	#N/A	#N/A	#N/A	#N/A	B16: =VLOOKUP($A16,BusType!$A$1:$F$200,COLUMN(B16),0)

Similar Worksheet Functions (#Functions)

HLOOKUP,  INDEX,  LOOKUP,  MATCH,  CHOOSE 

Related Sites (#related)

• Excel -- Worksheet Functions -- VLookup, Contextures.com « , Debra Dalgleish, better explanation of parameters
• Look up on two fields with this VBA alternative to VLOOKUP «, Andy Wiggins, uses the worksheet functions index and match internally, Byg Software    =VLOOKUP2(lookup_value1,lookup_value2,table_array,col_index_num)
  Based on Q59482 -- XL: Using INDEX and MATCH Against Multiple Criteria

Related Articles (#articles)

• Re: Vlookup or MatchIndex Niek Otten (2000-07-02), provides a nice summary of MATCH, VLOOKUP, and INDEX in terms of efficiency be sure to look at thread.
• Find Best Price With Excel INDEX and MATCH » Contextures Blog, Debra Dalgleish
• Looking Up Data In Tables, Chip Pearson, formulas to handle looking up data in lists that VLOOKUP can’t handle.
• Speeding up VLOOKUP in a large table when False is used as an argument, a reply post by Harlan Grove.
• Make VLOOKUP provide the next highest value (value above) if not an exact match, a reply post by Harlan Grove, and a reply by J.E.McGimpsey.  Message-ID replacements (“#”, “%23”), (“$”, “%24”), (“%”, “%25”)

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved