two dimensional formulas

[jsflbot]

Hello!

I'm trying to do a two-dimensional formula by combining the VLOOKUP and MATCH functions. What I'm trying to do is to have the formula fetch from the raw data (another tab); look up the UPC code and then see if that upc has sales under a specific store. UPC's are listed vertically and stores are listed horizontally, so I'm trying to find a match.

RAW DATA spreadsheet/tab:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item Description[/TD]
[TD]UPC[/TD]
[TD]Miami Beach Store [/TD]
[TD]Plantation Store[/TD]
[TD]Coral Store[/TD]
[TD]Jenkings Store[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Red Dragon[/TD]
[TD]005[/TD]
[TD]$4[/TD]
[TD]$10[/TD]
[TD]$65[/TD]
[TD]$10[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Blue Dragon[/TD]
[TD]004[/TD]
[TD]$5[/TD]
[TD]$4[/TD]
[TD]$9[/TD]
[TD]$50[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Yellow Dragon[/TD]
[TD]003[/TD]
[TD]$4[/TD]
[TD]$1[/TD]
[TD]$6[/TD]
[TD]$9[/TD]
[/TR]
</tbody>[/TABLE]


This is the formula that I'm using but its coming back as #N/A...

=VLOOKUP([@[Top 5 SKU''s]],'Raw Data'!A2:F4,MATCH([@Store],'Raw Data'!C1:F4)+1)

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Store[/TD]
[TD="align: center"]Top 5 SKU'S[/TD]
[TD="align: center"]Sales from Raw Data[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Miami Beach[/TD]
[TD]there's a drop down menu here
[/TD]
[TD]=VLOOKUP([@[Top 5 SKU''S]],'Raw Data'!A2:F4,MATCH([@Store],'Raw Data'!C1:F4)+1)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Plantation[/TD]
[TD]there's a drop down menu here[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Coral[/TD]
[TD]there's a drop down menu here[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

• TOP 3 SKU's refers to a cell where I have a drop down menu listing the top 3 sku's, so I would select a sku and hopefully cell C2 would pull that data that I need from the Raw Data spreadsheet

Any help is appreciated, thanks!!

 

[Andrew Poulsom]

Close:

=VLOOKUP([@[Top 5 SKU''S]],'Raw Data'!$A$2:$F$4,MATCH([@Store]&" Store",'Raw Data'!A$1:F$1,FALSE),FALSE)

 

[jsflbot]

I tried it Andrew and it did not work...

 

[Andrew Poulsom]

In what way didn't it work? I tested it with your sample data before posting.

 

[jsflbot]

Now it did, sorry Andrew! I was missing a parenthesis