Collect date on sheet 2 if tag on sheet 1 is contained within tag column on sheet 2

amber.lange

Hi, I'm stumped on a formula and hoping to get some help.

I have 2 sheets; Sheet 1 is titled E&I and contains the tag number and Sheet 2 is titled Open PO's and contains the date the item is due and the tag number with additional characters in a single cell. I am trying to pull in the date from the Open PO report into my E&I report if the tag number is contained within the column.

I've tried multiple formulas but this is what I currently have in Sheet 1: =IF(CONTAINS([Tag No]@row, {Open PO's Partno}), COLLECT({Open PO's Due Date}, {Open PO's Partno}, [Tag No]@row), "Received")

Paul Newcome

In that case we need to switch over to an INDEX/COLLECT so we can work in a CONTAINS function. How does this work for you:

=IFERROR(INDEX(COLLECT({Open PO's Due Date}, {Open PO's Partno}, CONTAINS([Tag No]@row, @cell)), 1), "Received")

Paul Newcome

Try an INDEX/MATCH instead.

=IFERROR(INDEX({Date Column}, MATCH({Number Column}, [Tag No]@row, 0)), "Received")

amber.lange

Hi Paul, that gave me an incorrect argument error. I was thinking the formula needed to use "contains" because the tag number (column Fpartno) on the sheet with the due dates also includes a prefix for the supplier. I tried using CONTAINS in your formula suggestion but that didn't work either. Below are my sheet references if that's helpful.

Thank you for your assistance.

Paul Newcome

Sorry. I do that every time. Switch the cross sheet reference and cell reference within the MATCH function.

I have

.....MATCH({Range}, column@row, .....

But it should be

.....MATCH(column@row, {Range}, .....

amber.lange

Paul,

It's still giving the incorrect argument error.

Paul Newcome

Ah. Ok. Parenthesis are a little off, and you're going to want that zero there at the end of the MATCH function. See below.

=INDEX(.....MATCH(....., ....., 0)), "Received")

amber.lange

Hi @Paul Newcome. This is the formula I have, which I think matches what you suggested, but it tells me everything is received, which is incorrect.

=IFERROR(INDEX({Open PO's Due Date}, MATCH([Tag No]@row, {Open PO's Partno}, 0)), "Received")

I tried manipulating the formula and got some dates returned, but they were not the correct dates, nor do I know where those dates came from since they aren't in the data source.

=IFERROR(INDEX({Open PO's Due Date}, MATCH([Tag No]@row, {Open PO's Partno})), "Received")

Is it something with the MATCH function? The tag numbers do not 100% match between the sheets because one sheet includes the vendor prefix, the other does not.

I'm sorry for all the back and forth but appreciate your help.

Paul Newcome

Are you able to provide screenshots for both? I didn't realize it would not be an exact match due to additional characters within the string.

amber.lange

I thought I mentioned that, my apologies. Below are screenshots. It's not consistent on the naming convention prefex in the Fpartno column. Some people put a comma after the vendor and on PUMP-110 it says "options" after the tag number because we cannot have duplicates within a job, if that makes sense. Overall, the "Tag No" column will 99% of the time be contained within "Fpartno".

Paul Newcome

In that case we need to switch over to an INDEX/COLLECT so we can work in a CONTAINS function. How does this work for you:

=IFERROR(INDEX(COLLECT({Open PO's Due Date}, {Open PO's Partno}, CONTAINS([Tag No]@row, @cell)), 1), "Received")

amber.lange

That works! Thank you for all your time on this; it will be very helpful going forward.

Paul Newcome

Happy to help. 👍️