Formula to look up a corresponding field from a related table?

Hmmm, so are you saying that the last six characters of the account number are in fact unique and in theory could be used as the Key field of the Account Record? Have you checked that they are unique or you just know they must be due to the natur if the data.

It's a VIN #, and many databases use last 6 to search because they are unique

I see two alternatives.
The first is that if you just want to display information on a child form as an embedded report, then you do not need even a relationship.  

You create a Report Link field and set it up so that in the configuration, the two fields match, for example the 6 characters that the user enters compares with a formula field on the VIN table which has a formula such as Right([VIN],6)   

However, while you can see the report of the parent VIN record on the child record, those fields are not able to be used in any formuals on on a child report.

The alternative is to use the last 6 characters of the VIN which according to Wikipedia are a unique serials for cars made /in the US https://en.wikipedia.org/wiki/Vehicle_identification_number

as the Key field to the VIN table.

There are two ways to make that happen.  One way is to make two formula fields to separate out the parts of the VIN between the serial # portion and the identification / check digit portion.  Then change them to text fields and going forward, you will input data in two fields.  Then set the Key field to be the serial #.

The other way, is that in fact, you can set the key field of a table to be a formula field, which would allow you to keep your data entry the same, as it may be from some imported source.

If you wanted to do that, I suggest making a copy of your app first and testing you import process after the change is done.

To change the key field to be a formula field is a bit advanced.  You  would just type the API string right into the URL on your browser along with an app token (or disable app tokens while you do this).  https://help.quickbase.com/api-guide/index.html#setkeyfield.html

The report won't work because I need the data to be stored in the vehicle record. Additionally, I cannot change the key field, because it would affect my integration with another system. I'm thinking perhaps making the vehicle record table a child table to a table where the last 6 of the VIN is the key field. I'm still trying to play this through in my head, so if you can contribute any pros or cons, that would be great!

And I just realized that I cannot generate a parent record from child record data... :(

You can create a Sync table which would update every hour and would have the serial # as its Key field.  So it would be a mirror of your main table with selected fields (or all fields).  

But then the records would be stored in the sync table when I already have all of my relationships connected to the main table. Thinking about it, though. It might be worth it to have the sync table and rewire everything to that while the app is still young.

I don't really know of the full scope of your app, so you would have to decide, but if there are just a few fields that you need from the VIN table, then the Sync table would allow that to happen without changing "all your other" relationships.  I don't know how many relationships would need to be redone.