Index and Match for multiple instances

ddandrea

Hi everyone,

I'm stuck with a formula that partially works for what I originally intended. However, it is only returning the first value it finds. Let's say I have a sheet and I paste a name in a column called 'Contractor Name'. Then, the column I have my formula in, let's call it 'Archive Match'. I want the formula in Archive Match to search another sheet, let's call that sheet Archived Requests, for the name that was pasted in Contractor Name and I want it to also index/look at another column called Request Type and let me know the data that's there.

This is what I have so far for the formula in my Archive Match column:

=INDEX({Request Type}, MATCH([Contractor Name]@row, {Requested For}, 0)))

It works. It checks the name pasted in Contractor Name and then looks into the Archived Requests sheet for that name and returns the Request Type. I also use IFERROR to remove all the no matches I find.

However, the issue is, what if that name appears later down in that list in the Archived Requests sheet? The formula wont find it and potentially I will be at a loss of knowing all the types of requests this person has had. I'm thinking a JOIN/COLLECT combination might be the answer, but I'm unsure of where to start.

For more context, this sheet is being actively worked in by myself and my colleagues. We check to see if a contractor needs an extension to their assignment. If we have already received an extension request then it will show up. I hope that makes sense. This would be making our work process easier and thus we would rather have this formula working in the sheet rather than run a report or something of that nature.

Here's a screen shot just you can get an idea of what I am talking about.

Please let me know if there's a way to search for multiple instances of a name and also use index to find data in another column for that name. If you have any questions, please let me know.

Thank you,

Dominic

Krissia B.

Hi @ddandrea

Thank you for additional clarification! See what I have come up below & the sample.

Formula used: =IFERROR(INDEX(COLLECT({Contractor Name}, {Request Type}, [Request Type]@row, {Within 6 Months}, [Within 6 Months?]@row), 1), "")

I'm using an IFERROR function to just leave the cell blank if there's an error message that shows up because the Request Type & Within 6 Months doesnt match anything from source so an error message may occur.

Let me know if I missed anything!

Cheers~

Krissia

ddandrea

Dear @Krissia B

I did modify your formula slightly, but it worked! Thank you so much for clarifying the Index(Collect formula for me! It's much more clear to me now how to set up the range with multiple criteria.

Cheers,

Dominic

ddandrea

I was able to get a bit further using Index/Collect see screenshot below for an example. The name below shows up multiple times in our archives

=INDEX(COLLECT({Request Type}, {Requested For}, [Contractor Name]@row, {Request Within 6 Months?}, Yes@row), 1)

However, now I need a column with "Yes" for each row as a criterion... :P

Krissia B.

Hello @ddandrea

Thank you for providing screenshots on this! Upon reviewing and testing, ill need a little bit more clarification on what you are trying to pull from the other sheet. Is the screenshot you provided just the target sheet or theyre both the source & target? If so, which one is the source and which one is the target? For adding the "Yes" in the criterion, is it based on "If its a YES and they have data in the Contractor Name & Archive Offboard or Extensions, provide the Request Type? What is the request type? Or is it the other way around where you want to pull the Contractor Name? Please provide additional details and screenshots if needed ( block out sensitive data ) to further review and assist with a resolution.

Thank you!

Cheers,

Krissia

ddandrea

Hi Krissia,

Thanks for the reply! The screenshot above is the target sheet. What I am trying to pull from the source sheet (our archives) is what type of request it was and was that request within the last 6 months. The source sheet (archive) has these two columns of data.

I want to be able to paste the name in the target sheet, like I did in the screen shot above, and have it check the archive sheet by name and also check the two columns of data (Request Type, and 6 Months?)

I hope that makes sense.

Thank you,

Dominic

ddandrea

Please disregard my 2nd comment and the 'Yes' column. That did not appear to work as I thought it did...

Krissia B.

Hi @ddandrea

Thank you for additional clarification! See what I have come up below & the sample.

Formula used: =IFERROR(INDEX(COLLECT({Contractor Name}, {Request Type}, [Request Type]@row, {Within 6 Months}, [Within 6 Months?]@row), 1), "")

I'm using an IFERROR function to just leave the cell blank if there's an error message that shows up because the Request Type & Within 6 Months doesnt match anything from source so an error message may occur.

Let me know if I missed anything!

Cheers~

Krissia

ddandrea

Dear @Krissia B

I did modify your formula slightly, but it worked! Thank you so much for clarifying the Index(Collect formula for me! It's much more clear to me now how to set up the range with multiple criteria.

Cheers,

Dominic

Krissia B.

I'm glad it worked out! @ddandrea 😊