Pulling Data Based on Certain Criteria

John- Michael Diedrich · 05/13/22

Hello Community,

I have a situation where I am trying to pull data from 3+ columns based on a few input fields (city, country, engineer level). We want to output the primary company, secondary company, and third company once this data is inputted.

On my base sheet, I have a City, State, Country, Engineer Level, Primary Company, Secondary Company, Third Company that has all of the information (picture below, there are other columns as well but they can be ignored).

On my input sheet, I have the same columns for the user to input the specific data where we want to then output the primary, secondary, and third company based on what they have inputted (picture below).

Any idea on how to pull the data we want to see? I was thinking a VLOOKUP but I am not sure if that will work for this situation.

Any help here is appreciated!

Leibel S · 05/16/22

@John- Michael Diedrich

You can to use a INDEX/COLLECT combo to get this done. Something along the lines of below. Please note you need to match up the references to how you made them by you

=INDEX(COLLECT({Primary Company REFERENCE RANGE}, {City Ref Range},[City]@row,{State Ref Range},[State]@row, {Country Ref Range},[country]@row,),1)

=INDEX(COLLECT({Secondary Company REFERENCE RANGE}, {City Ref Range},[City]@row,{State Ref Range},[State]@row, {Country Ref Range},[country]@row,),1)

=INDEX(COLLECT({Third Company REFERENCE RANGE}, {City Ref Range},[City]@row,{State Ref Range},[State]@row, {Country Ref Range},[country]@row,),1)

Rodolfo Garcia · 05/13/22

Hello John

Try it using INDEX and MATCH

these tools are better than VLOOKUP

I set several Database (10 Rules OPS), then in this image, I Index for the outcome 10RBScore, that match the regarding Nomina.

For your case try to use a 1st Co, 2nd Co and 3er Co or even better put the Location name, is just and Idea.

Regards

Rodolfo Garcia

Rodolfo Garcia · 05/13/22

@John- Michael Diedrich

Rodolfo Garcia · 05/13/22

@John- Michael Diedrich please let me know if it works

John- Michael Diedrich · 05/16/22

https://community.smartsheet.com/discussion/comment/329636#Comment_329636

Hey Rodolfo, thank you for the help here but I am still trying to write the INDEX formula correctly. Please see what I currently have below (the range is on the base sheet from the Country to the Primary Company for now). I am being told INCORRECT ARGUMENT currently. Any help here is appreciated!

Andrée Starå · 05/16/22

Hi @John- Michael Diedrich

I hope you're well and safe!

Try something like this structure.

=
INDEX({ColumnWithTheValueYouWantToShow}, 
MATCH(CellThatHaveTheValueToMatch@row, 
{ColumnWithTheValueToMatchAgainsTheCell}, 0))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Leibel S · 05/16/22

@John- Michael Diedrich

You can to use a INDEX/COLLECT combo to get this done. Something along the lines of below. Please note you need to match up the references to how you made them by you

=INDEX(COLLECT({Primary Company REFERENCE RANGE}, {City Ref Range},[City]@row,{State Ref Range},[State]@row, {Country Ref Range},[country]@row,),1)

=INDEX(COLLECT({Secondary Company REFERENCE RANGE}, {City Ref Range},[City]@row,{State Ref Range},[State]@row, {Country Ref Range},[country]@row,),1)

=INDEX(COLLECT({Third Company REFERENCE RANGE}, {City Ref Range},[City]@row,{State Ref Range},[State]@row, {Country Ref Range},[country]@row,),1)

Pulling Data Based on Certain Criteria

Best Answer

Answers

Help Article Resources

Categories