Using nested MATCH/INDEX across multiple columns

Joshua Peacock

Hi,

I'm using two sheets to track the distribution of transportation gift cards, one will be a master list of the cards and the other is a webform tracker that distribution partners use to log who receives cards.

I want the master list to check off each card given out after it is logged into the tracker, however each recipient may receive 1-7 cards so I need the master list to pull from the entire tracker to find the card. The tracker webform needs to be centered around the recipient so each row is another person, not another card.

I am able to do what I need with one column, but I cannot figure out how to get it to work across the table. Here's what the webform sheet looks like:

The master list is using the nested function to label who was given each card by card number, then checks off that the card has been given out (the current function just checks Resource Type 1 in the webform sheet so I can show you what I've done so far):

I originally tried searching the entire table with the MATCH function, but it just ended up a mess looking like this (everything is the same as the function in the second picture, just the range in the MATCH):

Not really sure how to get this to work, could use your thoughts.

Joshua Peacock

Picture is kind of small so here is the function in the second screenshot:

=INDEX({Pilot Resources Issued Client Names}, MATCH([Card Number]1, {Resource 1}, 0))

 

Paul Newcome

You could try using an IF statement to determine which range you want the match to look at. Something along the lines of...

 

=IF(COUNTIFS({Resource 1}, @cell = [Card Number]@row) > 0, INDEX({Pilot Resources Issued Client Names}, MATCH([Card Number]@row, {Resource 1}, 0)), INDEX({Pilot Resources Issued Client Names}, MATCH([Card Number]@row, {Resource 2}, 0)))

 

Basically this says to count how many times that card number appears in the {Resource 1} range. If it is more than 0 times meaning it exists in {Resource 1}, then it will run the INDEX/MATCH on {Resource 1}. Otherwise it will run it on {Resource 2}.

Joshua Peacock

Hi Paul,

Thanks for the advice. When I plug in the text you gave me it comes back as #UNPARSEABLE though. Would I need to change the first @row to be the specific Card number in the Resource Tracker? I haven't used @ before so I'm not 100% that I am using it correctly.

 

Thanks!

Paul Newcome

Joshua,

 

Here's a breakdown of each piece of the formula:

 

{Resource 1}: Cross sheet reference to the [Resource 1 Code Number] column on the Pilot Resources Issued sheet.

 

@cell: Leave as is. This basically gives the formula something specific to refer to when looking at the {Resource 1} range.

 

[Card Number]@row: Leave as is. This is saying to refer to whatever row the formula is on in the [Card Number] column in the Transportation Pilot Resource Tracker sheet.

 

{Pilot Resources Issued Client Names}: Cross sheet reference to what I assume is the [Pilot Resources Issued Client Names] column on the Pilot Resources Issued sheet. I pulled it from your original formula assuming that column was housing the name you were trying to pull.

 

[Card Number]@row: See Above.

 

{Resource 1}: See Above.

 

{Pilot Resources Issued Client Names}: See Above.

 

[Card Number]@row: See Above.

 

{Resource 2}: Cross sheet reference to the [Resource 2 Code Number] column on the Pilot Resources Issued sheet.

 

The way my formula works is that it first looks at the Resource 1 column. If the card number is there, it runs an INDEX/MATCH on that row to pull the appropriate name. If the card number is not in the Resource 1 column, it will run an INDEX/MATCH on whatever row the card number shoes up in the Resource 2 column to pull the appropriate row.

 

The problem with your initial formula is that you are looking at multiple columns but not specifying which one to pull from. What I did is broke it down into two separate INDEX/MATCH formulas (one for range 1/one for range 2) and then used an IF statement to say that if the first one doesn't work, do the second one.

 

Hopefully this helps you tweak it so that it will work for you. You do need to properly establish each of the cross sheet references as well. If you are just typing in what I have, but not designating a range for the reference, the formula won't know where to look.

Joshua Peacock

Ah, thank you for the explanation!

Paul Newcome

Happy to help. 

AmberDEPHR

@Paul Newcome First, let me say thank you so much for breaking down the explanation. I have this working for two columns but I'm wondering if I could get a third. I've done it a few ways but can't quite figure it out. I'm sure that I must be getting the punctuation wrong. Could you explain how to add another column to the formula?

This works: =IF(COUNTIFS({FY 2023 Vacancy Tracking Reference1|Posting Number}, @cell = [Posting Number]@row) > 0, INDEX({FY 2023 Vacancy Tracking | Sent to Office}, MATCH([Posting Number]@row, {FY 2023 Vacancy Tracking Reference1|Posting Number}, 0)), INDEX({FY 2023 Vacancy Tracking | Sent to Office}, MATCH([Posting Number]@row, {FY 2023 Vacancy Tracking Reference 2|Alt #}, 0)))

But this one doesn't look at the second or third column: =IF(COUNTIFS({FY 2023 Vacancy Tracking Reference1|Posting Number}, @cell = [Posting Number]@row) > 0, INDEX({FY 2023 Vacancy Tracking | Sent to Office}, MATCH([Posting Number]@row, {FY 2023 Vacancy Tracking Reference1|Posting Number}, 0)), INDEX({FY 2023 Vacancy Tracking | Sent to Office}, MATCH([Posting Number]@row, {FY 2023 Vacancy Tracking Reference 2|Alt #}, INDEX({FY 2023 Vacancy Tracking | Sent to Office}, MATCH([Posting Number]@row, {FY 2023 Vacancy Tracking Reference 3|Alt Number}, 0)))))

Paul Newcome

@AmberDEPHR I'm not sure I follow. Are you referring to the second range/criteria set inside each of the MATCH functions?

AmberDEPHR

I was doing the same thing the person that asked the initial question was doing. I was able to use your explanation to create my formula. It works for the first two, but when I add a third "MATCH([Posting Number]@row, {FY 2023 Vacancy Tracking Reference 3|Alt Number}, 0)))))" it breaks.

Paul Newcome

@AmberDEPHR It looks like maybe you forgot to close out a piece. Try moving some closing parenthesis from the end to finish out the part in BOLD.

=IF(COUNTIFS({FY 2023 Vacancy Tracking Reference1|Posting Number}, @cell = [Posting Number]@row) > 0, INDEX({FY 2023 Vacancy Tracking | Sent to Office}, MATCH([Posting Number]@row, {FY 2023 Vacancy Tracking Reference1|Posting Number}, 0)), INDEX({FY 2023 Vacancy Tracking | Sent to Office}, MATCH([Posting Number]@row, {FY 2023 Vacancy Tracking Reference 2|Alt #}, INDEX({FY 2023 Vacancy Tracking | Sent to Office}, MATCH([Posting Number]@row, {FY 2023 Vacancy Tracking Reference 3|Alt Number}, 0)))))