|re: "When I put that formula in, it is prompting me to select a file."|
Change the text Sheet1 in the formula to be the name of the sheet you are looking up the data from. i.e. the actual name of your Sheet1.
re: "The name column in Sheet1 is not identical to the name column in Sheet2."
What do you mean by not identical?
If the names are not in the same order, that's fine. If the columns don't contain the same names, then I wouldn't expect the formula to work since it's not going to find what it is looking for. This use of VLOOKUP requires matching names in each list to find what it is looking for.
Col A Col B
Why are you telling me what's in Columns A & B when your OP asked about Columns A, F & H?
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.