# MATCH Worksheet Function

Location:   http://www.mvps.org/dmcritchie/excel/matchtwo.htm
[View without Frames]

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

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 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”)