How to populate a table from a database

Hi there,

I was trying to populate a table sourcing the data from a database in another sheet but couldn't crack it.
I attached an example of what i'm trying to achieve: we have weekly orders with an amount of info (sheet 1); i'd like to populate the table in sheet 2 with the "Campaign number" from the sheet 1 (column G) at the intersection of dates and brand number (column D from the Sheet2).
Getting crazy with INDEX(MATCH()) and others but can't automate the process.
Thanks a lot
Renzo

Hello and welcome to the forum.

Here's one option:

E6 =IFERROR(1/(1/SUMIFS('Sheet 1'!$G:$G,'Sheet 1'!$B:$B,E$2,'Sheet 1'!$E:$E,$C6,'Sheet 1'!$F:$F,$D6)),"")

Man that's just perfect (and straight magic to me)
Thanks a lot

R

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

and straight magic to me

Let's look at the formula in E6.
=IFERROR(1/(1/SUMIFS('Sheet 1'!$G:$G,'Sheet 1'!$B:$B,E$2,'Sheet 1'!$E:$E,$C6,'Sheet 1'!$F:$F,$D6)),"")

Working from the inside out, SUMIFS takes the sum of column G where all of the following conditions are true:
1) Column B is 10/21/2018
2) Column E is Bloopies
3) Column F is 54993

Since there is only one row where all of these conditions are true, the campaign number for that row is returned.

If there were no rows where all of these conditions are true, the SUMIFS formula will return 0. This is where IFERROR comes in.

We can use the fact that 1/x will produce an error only when x=0 to return "" (blank) instead of 0.
Since we don't want to return, for example, 1/24061995 when the SUMIFS formula returns 24061995, we can use 1/(1/24061995) to return 24061995.

In other words, if 1/(1/SUMIFS(...)) returns an error, the formula will return "".
If 1/(1/SUMIFS(...)) does not return an error, the formula will return 1/(1/SUMIFS(...)) which is the same as SUMIFS(...).