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

Constance King

CountyBudgetTrackerSnip.png

CountyBudgetSummarySnip.png

BudgetCompRecdSnip.png

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.

SoS | Dan Palenchar

My interpretation of this is that you want your County Budget Summary Snip Sheet to pull values from either the Budget Tracker Snip sheet or the Budget Compr Recd Snip based on both the LDH and Grant.

To do this, I would use cross sheet INDEX(MATCH()) formulas. To facilitate this, I would add a hidden column to both of your source sheets that combines the LDH and Grant values to use a lookup criteria. Use:

JOIN(LHD@row:Grant@row)

Put this formula in every row that has data, lock it, and hide it. I'm going to call this hypothetical column "Lookup".

Now we can use a cross sheet index match to pull data into the from either sheet to the County Budget Summary Snip Sheet. Note that your primary column in this sheet is named LDH and not LHD. I am going to assume this a typo and use LHD.

=INDEX({Budget Action}, MATCH(JOIN(LHD@row:Grant@row), {Lookup}, 0))

Where {Budget Action} is a cross sheet reference to the "Budget Action" column and {Lookup} is a cross sheet reference to the lookup column constructed above.

You can reproduce this formula for any value you like, just replace {Budget Action} with whatever column you are interested in. If you are pulling from the other sheet, the {Lookup} reference will need to be changed to the source sheet as well.

SoS | Dan Palenchar

My interpretation of this is that you want your County Budget Summary Snip Sheet to pull values from either the Budget Tracker Snip sheet or the Budget Compr Recd Snip based on both the LDH and Grant.

To do this, I would use cross sheet INDEX(MATCH()) formulas. To facilitate this, I would add a hidden column to both of your source sheets that combines the LDH and Grant values to use a lookup criteria. Use:

JOIN(LHD@row:Grant@row)

Put this formula in every row that has data, lock it, and hide it. I'm going to call this hypothetical column "Lookup".

Now we can use a cross sheet index match to pull data into the from either sheet to the County Budget Summary Snip Sheet. Note that your primary column in this sheet is named LDH and not LHD. I am going to assume this a typo and use LHD.

=INDEX({Budget Action}, MATCH(JOIN(LHD@row:Grant@row), {Lookup}, 0))

Where {Budget Action} is a cross sheet reference to the "Budget Action" column and {Lookup} is a cross sheet reference to the lookup column constructed above.

You can reproduce this formula for any value you like, just replace {Budget Action} with whatever column you are interested in. If you are pulling from the other sheet, the {Lookup} reference will need to be changed to the source sheet as well.

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!