Can I find a match from "Sheet A" with all that applies from "Sheet B" and populate a "Sheet C"?

AndyFAFF

Hello. Any help would be appreciated, even if it is "Go get another program" or "too involved of a problem and you need to book an appointment."

Basically, I set up two separate sheets. Example: "Sheet A" has Companies that offers different types of fruit and have specific areas they need to supply: Apples, oranges, pears and bananas. "Sheet B" has the Farmers who have specific crops, some have Apples, some have just oranges, some have both apples and oranges, etc... Is there a formula that I could put in place in "Sheet C" that would provide matches for the data entered in "SHEET A" with "SHEET B"...providing information such as: "Company X could get apples from Farmer Y, Farmer Z, and Farmer R... Company B could get oranges and apples from Farmer Z and Farmer Q...

For Demonstration purposes, I kept it to farmers and fruit. In reality I'm matching up Summer Camp offers to match with candidate kids (Grade level, gender) from our program. I'm a non-profit and looking for the best way to organize over 30 educators trying to match their lists of eligible kids with the available spots.

Thanks for any consideration and thoughts. I really appreciate it. I've been trying to teach myself via videos and online info but I'm going down rabbit holes not knowing which formula would be easiest or the best suited. My trial and error returns a lot of "INCORRECT ARGUMENT" and "ARE YOU KIDDING ME?" and "WHAT, AM I SOME SORT OF MAGICAL GENIE THAT CAN READ YOUR MIND?" and "YOUR SYNTAX CONFUSES AND SCARES ME."

Thank you,

Andrew N.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/321101#Comment_321101

No worries. I just set this up in my test folder. I will walk you through it.

Here's my Campers sheet, pretty straightforward:

Here is my Summer Camps sheet:

Formulas on Summer Camps sheet:

CamperGrade, row 1 only:

=INDEX({Campers Grade}, MATCH([Camper Name]@row, {Campers name}, 0))

English: Find the campers grade value from the Camper sheet row where the Campers Name matches the Camper Name on this row.

The values in curly braces are references to columns on the Campers sheet. When building your formula, follow Smartsheet's prompts to Reference Another Sheet:

Do the same for the CamperGender cell in row 1:

=INDEX({Campers gender}, MATCH([Camper Name]@row, {Campers name}, 0))

So at this point, what happens when you enter a camper's name in to the top row on the Summer Camps sheet? These two formulas look in the Campers sheet and pull back the grade and gender info for that camper.

Now for the Match column. Starting in row 2 of the match column, enter the following formula:

=IF(AND(CamperGrade$1 >= [Starting Grade]@row, CamperGrade$1 <= [Ending Grade]@row, CONTAINS(CamperGender$1, Gender@row)), 1, 0)

Copy the formula to the rest of the match column below row 2.

English: IF the camper grade from row 1 is greater than or equal to the starting grade for the camp on this row, AND is less than or equal to the Ending grade for the camp on this row, AND the Gender field on this row CONTAINS the value in CamperGender row 1, then check the box; otherwise, do not check the box.

Lastly, select the CamperGrade, CamperGender, and Match columns and lock them.

Now, to see it in action:

I put in Wolfgang Van Halen, the formulas in CamperGrade and CamperGender pull his values of grade 3 and gender Boy. The checkboxes immediately are checked for camps are compatible with a 3rd grade boy, while the others are unchecked. Replace him with Geddy Lee (7th grade Boy) you get a different result set.

Now, if I add a filter to only show rows where Match is checked, that's what it returns for each camper I put in. (Note: You may have to Save/Refresh before it will change while in a filtered view.)

Once you're sure everything is working, you can hide the Camp Number, CamperGrade and CamperGender fields.

Garrett Henke

@AndyFAFF Does sheet A, B, and C have a unique row value that is the same across all sheets? For your example does Company X have a value that matches a value from Farmer Y, Farmer Z, and Farmer R? There needs to be some sort of lookup value that can link all of the sheets together for this to work

Jeff Reisman

Firstly, your choices of error messages should be standard! 😅

Here we will create a system to generate a list of matching camps for a camper!

Sheet A - Summer Camps:

Text/Number Column: Camper Name.

Auto-Number column, numeric only, starting at 1: Camp Number.

Text/Number Columns: Camp Name, Starting Grade, Ending Grade.

Multi-select column: Gender (with choices of Boys, Girls, Other.)

Hidden Text/Number Columns: CamperGrade, CamperGender.

Hidden Checkbox column: Match

Check the Match checkbox on Row 1 and lock the row.

Starting on Row 2, Add your camps and each camp's info.

Sheet B - Campers:

Text/Number Columns: Camper Name, Grade. Single-select Gender column with choices of Boy, Girl, Other.

List all your campers on this Sheet B.

Back on Sheet A:

In the CamperGrade column on Row 1 only: =IFERROR(INDEX({Sheet B Grade col ref}, MATCH([Camper Name]$1, {Sheet B Camper Name col ref}, 0)), "")

In the CamperGender column on Row 1 only: =IFERROR(INDEX({Sheet B Gender col ref}, MATCH([Camper Name]$1, {Sheet B Camper Name col ref}, 0)), "")

In the Match column on all rows starting at Row 2:

=IFERROR(IF(AND(CamperGrade$1 >= [Starting Grade]@row, CamperGrade$1 <= [Ending Grade]@row, Gender@row, CONTAINS(CamperGender$1)), 1), "")

Now create a Report. Use Sheet A as the basis for the report, include the Camper Name, Camp Number, Camp Name, and Match columns. Filter for only rows where Match is checked.

Open the Report, only the empty top row should be there. Enter the Camper Name on the top row. Save. Refresh. Your matching camps should appear in the report. Export the report to save your list for that camper. Rinse and repeat.

AndyFAFF

I'm eager to try this and can't thank you enough...I'll let you know how it goes.

AndyFAFF

@Jeff Reisman Thank you, I'll be giving it a go. @Garrett Henke thank you, Garrett, yes--I've made them unique values across the rows for each chart.

AndyFAFF

@Jeff Reisman I'm probably dealing with some pilot error. I'll go slowly and check it all over again. Until then, these are some quick questions (I hope)

I want both sheets chosen for the Report, correct?

Should the Primary Column have specific Data?

You indicate that I should put that third formula in the MATCH column starting with the second row...if it's locked, I am able to put it in but then it reads UNPARSEABLE.

I should be able to figure out the logic by looking it over and connecting the dots--and that's something I'll do, but I put these three questions out there in case you were around the Smartsheet community. Thank you for what you've done so far. I'm learning as I go.

Jeff Reisman

I want both sheets chosen for the Report, correct?

Just choose Sheet A for the report. We're going to use this report as a lookup system. You give it a camper name, the formulas in Sheet A (camp list) look at Sheet B (campers) to find the details about the camper you entered. Each row will consider: Is the camper grade on row 1 between my starting grade and ending grade? Is the camper gender on row 1 found in my gender list? If the criteria are met, the MATCH box gets checked. Since the report is filtered to only show rows where MATCH is checked, you see the matching camps for that camper. (I'll put up some screenshots of a similar lookup report I just built for a dept at my company in the next post.)

Should the Primary Column have specific Data?

Unlike Excel, Access, SQL, Oracle, etc., The Primary Column in Smartsheet is not particularly important. I try to put the most unique info on the sheet in this column, and it can only be Text/Number, but I haven't found that it's terribly important. It automatically gets included in reports from the start, but doesn't need to be shown or considered in criteria. If it were me, I'd have the Camp Name or some other Camp ID in the primary column in Sheet A, and the Camper Name or camper ID in the primary column in Sheet B.

You indicate that I should put that third formula in the MATCH column starting with the second row...if it's locked, I am able to put it in but then it reads UNPARSEABLE.

In Sheet A, make sure MATCH is a checkbox column, then create your formula in the MATCH column. Convert it to a column formula (right click, down at bottom select convert to column formula.) Save the sheet. In the cell at MATCH row 1, right click and convert back to cell formula. Remove the formula from the cell in row 1. Check the box, then lock the row (not the column, just row 1.)

Jeff Reisman

At my company, I built a Smartsheet system that is used for collecting data about new products being launched. Product managers enter initial information and kick off a workflow which goes to demand planners, logistics, finance, trade compliance, business planning, etc., with each area adding or validating data. Eventually this will feed directly into our financial and inventory system, but for now, the collected data has to be entered manually into that other system. Some of that data is in the primary sheet, some is generated via lookup values from another reference sheet, and some is extra data from a supplemental sheet, so expecting our supply chain admin to go to three sheets, scroll across dozens columns, all without transcribing errors was an impossible ask. Not to mention that the order in which data is collected does not correspond to her entry screens! So... I decided to build a report that lets her put in the product number, and have the system do all the lookups, and present the data in the proper order for her data entry, top to bottom down the page, and only the data for that product.

I decided to build a lookup sheet to start this. Now my main sheet has 90+ columns and about 4500 rows... So there's a limit to how many INDEX/MATCH or cell links I can put in my lookup sheet. Meaning I needed two lookup sheets. Plus there's my other reference sheet to think about... so three underlying lookup sheets are needed for this report.

Lookup Sheet 1:

I have a cell for entering the Material number on Row 1. I list my field names, and then INDEX/MATCH formulas in Value based on the Material number. I also pull a product hierarchy from my main sheet, which is used for lookups from a Hierarchy sheet. Where it says Data Match!, I have a formula that is comparing the values for Material and Hierarchy to my other two lookup sheets to be sure they match, meaning I'm showing data for the same product on all three lookup sheets. The Sort column is used to intermingle these rows with rows from the other sheets in the report, so they're all in proper data entry order.

Same thing for the second underlying lookup sheet and the third:

Now on my report, I filter by rows where Value is not blank (there's a white X you can't see in the top row in the second and third sheets,) and sort by the sort column. This puts my top row from each underlying sheet at the top of the report, and eliminates rows that have no data for her to enter.

When my admin copies the Material number into the report, she copies to the first three rows and saves, pauses, and refreshes until the box at the top says "Data Match!" This places the material number in the top row for each underlying sheet, which the formulas use for their lookups. It takes about 2-5 seconds for all the formula values to update and be reflected on the report.

Jeff Reisman

You might need to switch around the CONTAINS portion in the match formula, so that range and criteria are contained within the CONTAINS:

=IFERROR(IF(AND(CamperGrade$1 >= [Starting Grade]@row, CamperGrade$1 <= [Ending Grade]@row,  CONTAINS(CamperGender$1, Gender@row)), 1), "")

AndyFAFF

@Jeff Reisman, I've failed you. I've been at this for quite a long time. I think I need to read over and study your example. Something I'm doing is wrong, but, I am very, very thankful for your guidance and help.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/321101#Comment_321101

No worries. I just set this up in my test folder. I will walk you through it.

Here's my Campers sheet, pretty straightforward:

Here is my Summer Camps sheet:

Formulas on Summer Camps sheet:

CamperGrade, row 1 only:

=INDEX({Campers Grade}, MATCH([Camper Name]@row, {Campers name}, 0))

English: Find the campers grade value from the Camper sheet row where the Campers Name matches the Camper Name on this row.

The values in curly braces are references to columns on the Campers sheet. When building your formula, follow Smartsheet's prompts to Reference Another Sheet:

Do the same for the CamperGender cell in row 1:

=INDEX({Campers gender}, MATCH([Camper Name]@row, {Campers name}, 0))

So at this point, what happens when you enter a camper's name in to the top row on the Summer Camps sheet? These two formulas look in the Campers sheet and pull back the grade and gender info for that camper.

Now for the Match column. Starting in row 2 of the match column, enter the following formula:

=IF(AND(CamperGrade$1 >= [Starting Grade]@row, CamperGrade$1 <= [Ending Grade]@row, CONTAINS(CamperGender$1, Gender@row)), 1, 0)

Copy the formula to the rest of the match column below row 2.

English: IF the camper grade from row 1 is greater than or equal to the starting grade for the camp on this row, AND is less than or equal to the Ending grade for the camp on this row, AND the Gender field on this row CONTAINS the value in CamperGender row 1, then check the box; otherwise, do not check the box.

Lastly, select the CamperGrade, CamperGender, and Match columns and lock them.

Now, to see it in action:

I put in Wolfgang Van Halen, the formulas in CamperGrade and CamperGender pull his values of grade 3 and gender Boy. The checkboxes immediately are checked for camps are compatible with a 3rd grade boy, while the others are unchecked. Replace him with Geddy Lee (7th grade Boy) you get a different result set.

Now, if I add a filter to only show rows where Match is checked, that's what it returns for each camper I put in. (Note: You may have to Save/Refresh before it will change while in a filtered view.)

Once you're sure everything is working, you can hide the Camp Number, CamperGrade and CamperGender fields.

AndyFAFF

@Jeff Reisman NOT only did you spell it out for me, you gave me a vocabulary, the confidence, and a much better understanding of this whole process. I understand this SO MUCH better. I think I was getting confused with the initial IFERROR and running the reports--I am so very thankful for the extraordinary amount of time, patience and generosity of your knowledge. I'm flabbergasted at the willingness to help a fellow Smartsheet user who is a novice at formulas, but a veteran at cutting and pasting and rigging a sheet to get me by. Can't wait to start streamlining and making Smartsheet work for me.

Much, much thanks Jeff!

In your debt,

Andy

Jeff Reisman

https://community.smartsheet.com/discussion/comment/321250#Comment_321250

My pleasure. Help isn't always help if the person doesn't understand the WHY and the HOW, so I try to pass that on in my answers. I really like solutioning, and taking what I've helped others figure out has gotten me through many challenges with my own systems, so everybody wins.