excel - Find an exact match in an excell workbook then copy data from that row to another workbook

Question

I have two excell workbooks (wpt.xls & route.xls) that I'm using to make Waypoint & Route files. I would like to automate the process a bit by using a macro to find the row that contains "A-1" in column F in workbook (wpt.xls) then I would like to paste the data from column A into my other workbook (route.xls) into cell A1. My sheet looks like the one below.

A   B       C       D               E          F
1   1   1   26-41.3143-N    082-52.0707-W   B-2
2   1   1   26-43.1669-N    082-47.6695-W   Z-3
3   1   1   26-49.7169-N    082-53.1127-W   Z-7
4   1   1   26-42.4121-N    082-51.9514-W   A-1

In this example I need "4" to be copied and pasted to my workbook (route.xls) into the the cell A1.

Answer 1

This will work:

=INDEX([wpt.xlsx]Sheet1!$A$1:$A$4,MATCH(A1,[wpt.xlsx]Sheet1!$F$1:$F$4,0))

Enter that on the sheet in your route sheet, at the moment it looks in cell A1 for the item in col F you want.