I’m going to guess that the two queries don’t always return the same list of plants, correct? Otherwise, it’s pretty trivial.
The correct way to solve this is at the query level. Create a query that joins the two data sets, and bring that into your dashboard. But, since that is both fairly obvious and fairly trivial to accomplish, I’m going to assume that it’s also not an option for you for whatever reason.
You can solve this with VLOOKUP (or, more properly, INDEX/MATCH), but it requires solving one important and tricky problem first. You need to get the combined list of plants.
If you are lucky, one of your queries will always only have rows for plants that exist in the other query. Let’s assume that is the case, and Query B (the one with key4 and key5) is always a subset of the plants in Query A (the one with key1, key2, and key3).
[list]Bring in Query A to Excel, say in columns A through D
Bring in Query B to Excel, say in columns AA through AC
In row 2 of column E, put this formula: INDEX(AB2:AB500, MATCH(A2,AA2:AA500,0))
In row 2 of column F, put this formula: INDEX(AC2:AC500,MATCH(A2,AA2:AA500,0))
Copy row 2 of E and F down to fill in the whole data set
[/list]
You should probably add some checks in there to convert blanks and #N/A to prettier or more useful values, but that’s the core.
If you could have some plants that show up in Query A and not in Query B, AND some plants that show up in Query B and not in Query A, you’re going to have a bad day. It is possible to make this work in Excel. I saw a description for doing it once. But it’s not easy, and it has pretty terrible performance. Unfortunately, I don’t recall the details, but a Google search for relevant keywords like “full outer join” might turn it up.
Lugh (BOB member since 2009-07-16)
(BOB member since 2012-04-05)