Vloopup + INDEX and transpose

Question

I really need help with the following situation . I'm trying to use a vlookup or index that I believe is really advance for me , unfortunately I don't have the knowledge how to attached an excel file here so I will add a image

I have a huge data base were I have names and the products they are buying , there are 5 products in total but not everyone is trying to buy all of them so there are a few people that only wants to buy 1 . The data base contains the information on a vertical way but I need to use a formula to get all the information related to each customer on a horizontal way.

On the image you will see my database from A4:C12 and also the type of products from A14:B18

I have highlithed with yellow from E7:O12 an example how the final table should look after entering the formulas.

I have tried combining vlookup or index but the problem is that not all the customers are buying the 5 products so for example Jorge is only buying 1 house which means the rest of the fields must be empty

From E1:O4 is where I need to enter the formulas.

Answer 1

This can be accomplished in 2 parts, one formula for the Product, and one for finding the QTY.

Product Formula

So starting with the Product formula I was able to accomplish this by using:

=IF(SUMPRODUCT(($A$5:$A$12=$E2)*($B$5:$B$12=INDEX($B$14:$B$18,MATCH(F$1,$A$14:$A$18,0))))>0,INDEX($B$14:$B$18,MATCH(F$1,$A$14:$A$18,0)),"")

Explanation

Assuming Product a falls under column F

I will explain this using Saul

This works by using two distinct arrays. One that checks the name by the name listed on the current row. The second array is checking the products based on the product column. So for the name array:

Adam  =? Saul => False
Adam  =? Saul => False
Adam  =? Saul => False
Adam  =? Saul => False
Adam  =? Saul => False
Saul  =? Saul => True
Saul  =? Saul => True
Jorge =? Saul => False

Now using INDEX($B$14:$B$18,MATCH(F$1,$A$14:$A$18,0)) we can get the name of the current product for each column. Using product A this array would look like:

Car      =? Car => True
House    =? Car => False
Clock    =? Car => False
Umbrella =? Car => False
Computer =? Car => False
Car      =? Car => True
House    =? Car => False
House    =? Car => False

Multiplying these two arrays surrounded by a SUMPRODUCT yields:

False * True  = 0*1 = 0
False * False = 0*0 = 0
False * False = 0*0 = 0
False * False = 0*0 = 0
False * False = 0*0 = 0
True  * True  = 1*1 = 1
True  * False = 1*0 = 0
False * False = 0*0 = 0
         SUMPRODUCT = 1

If this sum is 0 then the person is not ordering this product, if it is greater than 0 then the person is at least listed to buy the product. If you need an additional check (if the Qty is 0 perhaps) this can be augmented onto the array multiplication.

Using this inside an IF statement we can easily return nothing ("") or the Product using INDEX($B$14:$B$18,MATCH(F$1,$A$14:$A$18,0)) based on if the sum is >0 or 0. Thus arriving at our final formula.

QTY Formula

The QTY formula is fairly easy. Just use the SUMPRODUCT method described earlier but augment on the QTY column. Now it all depends if you want to show nothing ("") or 0 for when there is no Product.

To show 0:

=SUMPRODUCT(($A$5:$A$12=$E2)*($B$5:$B$12=F2)*($C$5:$C$12))

To show nothing:

=IF(F2="","",SUMPRODUCT(($A$5:$A$12=$E2)*($B$5:$B$12=F2)*($C$5:$C$12)))

Explaination

It is very simple, we just take the array that we arrived to earlier and multiply it across the QTY column:

     0 * 2 = 0
     0 * 3 = 0
     0 * 1 = 0
     0 * 2 = 0
     0 * 3 = 0
     1 * 2 = 2
     0 * 3 = 0
     0 * 1 = 0
SUMPRODUCT = 2

Adding the IF(F2="","",...) will check to see if a product is listed, if not then it will put nothing in the cell, else it will put the QTY.