MATCH Worksheet Function

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

Introductory Remarks   (#intro)

TABLE -- Sheet BusType

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

Solution using INDEX and MATCH

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

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


This page was introduced on February 5, 2000. 

[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

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


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