Can you index/match partial values

atewari1

I am working to index match project names based on matching PO values.

There are PO values of 4 digits, 7 digits, and more than 7 digits. The PO values greater than 7 digits all begin with 7 digits that are already listed in the working sheet. The reason they exceed 7 digits are for iterations of a PO value. EX: 1000001-001, 1000001-002, etc.

I currently have a nested IF statement with Index/Match to work out when the PO values are 4 digits and 7 digits. The issue I run into is if I want to match a PO that contains "-###" after it, I get a NO MATCH since the formula looks for an exact match. EX: Searching for 1000001 but getting a NO MATCH since the value is 1000001-001.

Formula:

=IF(LEN([PO#]@row) = 4, INDEX({(PDS) Procurement Delivery Scheduler Project Title}, MATCH([PO#]@row, {(PDS) Procurement Delivery PO Value Only}, 0)), IF(LEN([PO#]@row) = 7, INDEX({Site Contact Sheet Range 2}, MATCH([PO#]@row, {Site Contact Sheet Range 4}, 0)), INDEX({(PDS) Procurement Delivery Scheduler Project Title}, MATCH([PO#]@row, {(PDS) Procurement Delivery PO Value Only}, 0))))

*The site contact sheet contains 7 digit PO values that correspond to a given project.

*The procurement delivery scheduler contains 4 digit PO values corresponding to other projects.

I attempted an edit of the second IF statement to only match the first 7 characters of a PO value to avoid any of the excess characters but it does not yield what I want.

IF(LEN([PO#]@row) = 7, INDEX({Site Contact Sheet Range 2}, MATCH(LEFT([PO#]@row, 7), {Site Contact Sheet Range 4}, 0))

SteyJ

try with index/collect and use filtering using CONTAINS

=INDEX(COLLECT({Site Contact Sheet Range 2}, [PO#]@row, 
CONTAINS({Site Contact Sheet Range 4}, @cell), 1))

atewari1

@SteyJ Would the CONTAINS need to flip? If the range is the {Site Contact Sheet Range 4} and the search value is the PO itself in a given row?

Edit: I take that back I understand we need to search for the 7 digits amongst the greater PO values. My issue now is even when testing a different simple contains statement with numbers, I am getting an INVALID DATA TYPE error. Can CONTAINS be used with numeric values?