VLOOKUP for GTFS

Make sure both text files in each tab are correctly delimited by commas and have not had their values modified by Excel or Sheets. See "Opening a text file" on Navigating your GTFS.

Make sure the column is type=General (not Text, since we are going to use a formula). In the sample version, this is column E.

VLOOKUP formula are basically taking two matching 'keys' (in this case, stop_id) and returning other values associated with that 'key.' The formula goes on the sheet where you want the data to end up. There are four parts:

1. What is the key you want to match up (in this case, the stop_id in cell D2.)

2. Where should the formula look to find the matching key and the data you want returned (stopx.txt sheet, columns A through D)

3. When it finds a match, how many columns to the right, including this column, should it go to find the data you want returned (stop_name is in column D, so 4 columns away)

4. Should it return an approximate match (true) or an exact match (false) (you always want false.)

This formula looks like this:

=vlookup(D2,stops!A:D,4,false)

Excel and Sheets will both automatically move each value down to the next row (so D2 becomes D3, and so on.)

Because formulas will keep running, even after you have the data you want, you should save the work you've done by using copy-paste on the whole column. When pasting, use "Paste special," "Values only" so that the actual returned stop names stick around instead of the formula.