How to populate a contact column based on two other columns in the same sheet

Russ Onkeles-Klein (RI/GLOBAL)

Greetings all,

I could use everyone's expertise please to figure out an issue with populating a contact column based on two other columns in the same sheet. I'm close to a solution but have one final hurdle.

I have one column called "Purchase Requisition (PR) #" and another called "Contact" populated by a form where a user logs an initial PR# with their email (contact). At a later date, additional entries of the same PR# occur as part of a larger procurement system where multiple Purchase Orders (PO) are created via a form and the initial PR# number is entered manually. For reference, no users are in the datasheet, we have a Dynamic View setup, so we are not creating parent/child relationships.

I'm able to get this to work using an INDEX(MATCH) formula, however, it only works when the row with the PR# and contact appears first (higher up) in the sheet. If that row is below any PO/PR rows, then the contact column will not populate. I understand why this is happening based on formula mechanics. However, what I'm hoping to achieve is to populate the contact for any instance of the PR# whether it shows up first or not. I also understand one solution is to just keep the initial PR entry highest in the sheet but with many thousands of anticipated entries, I'm concerned that may not always be possible.

See pics below to help clarify.

Image below shows the Index Match formula working but only when the contact appears with the first PR#. Formula =INDEX([Contact from Form]:[Contact from Form], MATCH([Purchase Req]@row, [Purchase Req]:[Purchase Req], 0))

Image below shows the challenge were Abdul shows up for the second PR789 entry, not the first, and he won't populate in the Index/Match column. Same formula as above.

I appreciate your ideas and input!!

Thanks,

Russ

Leibel S

@Russ Onkeles-Klein (RI/GLOBAL)

Add an additional column Lets call it contact PRLookup.

It should have a column formula:

=IF([Contact from Form]@row<>"",[Purchase Req]@row,"")

Then the formula in the index match column will use this newly created column as the lookup:

=INDEX([Contact from Form]:[Contact from Form], MATCH([Purchase Req]@row, [PRLookup]:[PRLookup], 0))

Leibel S

@Russ Onkeles-Klein (RI/GLOBAL)

Add an additional column Lets call it contact PRLookup.

It should have a column formula:

=IF([Contact from Form]@row<>"",[Purchase Req]@row,"")

Then the formula in the index match column will use this newly created column as the lookup:

=INDEX([Contact from Form]:[Contact from Form], MATCH([Purchase Req]@row, [PRLookup]:[PRLookup], 0))

Russ Onkeles-Klein (RI/GLOBAL)

This is fantastic @Leibel S, thank you!! Works like a charm and solves the same issue I was having with PR# and Location of Procurement entries.