Index match with Left

BlueQuestion

I'm trying to make an Index/Match formula and I can use some help.

I have a Smartsheet with a list of parts (Sheet1). I also have a Smartsheet that has a list of part prefixes and a contact for parts beginning with that prefix (Sheet2).

I would like to do an Index match on my parts smarsheet (sheet1) so that any parts beginning with the letters/numbers referenced on sheet 2 return the contact from sheet 2. I don't want to use CONTAINS, since I only want the prefix of one sheet to match

Paul Newcome

What is your question?

BlueQuestion

@Paul Newcome - Sorry about that! I'm having trouble figuring out what formula to use and how to set it up.

I'm thinking I might need to use Index match with a Left function, but I can't seem to get it to work. Basically, I want the formula to look at my part number and tell me who the contact is, based on the prefix of the part. My sheet 2 is the "database" of prefixes and contact names, I just need to know how to set up the formula.

Paul Newcome

The update with details and screenshots definitely help. Give something like this a try:

=INDEX({Contact}, MATCH(LEFT([Part Number]@row, 2), {Prefix}, 0))