Combining information from 2 sheets based on matching data from 2 columns


I have multiple sheets tracking different aspects of grant submissions. I want to bring them all together into one sheet. I need to match items based on two columns and pull data from a third column. We have 24 counties and 2 grants that are being tracked. I want to put them all into one report where I can note if all components have been received (one sheet) in the same line as the budget action (dropdown of 4 items). I have tried using a variety of formulas from a VLOOKUP, to INDEX(MATCH()), to nested IF statements. The main error I have received is that it cannot be parsed. I would rather not combine the county and grant into one column, but could add it as a formula column. I have attached parts of the two sheets I am trying to combine into one. Unfortunately, I have deleted all the non-working formulas I have tried.

The formula would compare LHD and Grant from the new sheet with those columns from both of the indicated sheets and return a value from another column such as Budget Action (CountyBudgetTrackerSnip), All Components & All Appvd (BudgetCompRecdSnip) and put them into columns on the CountyBudgetSummary sheet.

My sheets only have two columns in common that is why I want to pull data from both into one report without having two lines for each County/Grant combination.

Best Answer


  • Constance King

    Thanks! I am learning to use hidden columns and create sheets to use for reporting. I'm hiding the sheets from users also as they confuse them when they have access to the folders!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!