Index match with Left

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
Answers
-
What is your question?
-
@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.
-
The update with details and screenshots definitely help. Give something like this a try:
=INDEX({Contact}, MATCH(LEFT([Part Number]@row, 2), {Prefix}, 0))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!