Offset formula within an array formula?

[nlarski]

I'm sure there is a pretty simple answer to my question but I cant seem to figure it out.

I have a list of products by store and their sales. I want to transfer the product from some stores into other stores that are actually selling it well.

Here is the array formula that I have which is returning the largest number of units sold (the best selling store's sales for that particular product)

{=LARGE(IF(SKU=G7,'Data Dump'!$Q$6:$Q$37155),1)}

What I would like to do is return the "best selling" store name instead of the number of units sold which sits 6 columns to the left. In order to do this I tried to put in the Offset function. Excel gives me an error pop up and I cant enter the formula.

<strike></strike>={OFFSET(LARGE(IF(SKU=G7,'Data Dump'!$Q$6:$Q$37155),1),0,-6)}


Does anyone know how to achieve what I'm trying to do? I may be overthinking this one but Ive spent too much time googling and thinking about it for now.


Thanks!

 

[barry houdini]

You can use OFFSET but INDEX is better and easier, e.g.

=INDEX('Data Dump'!$K$6:$K$37155,MATCH(LARGE(IF(SKU=G7,'Data Dump'!$Q$6:$Q$37155),1),IF(SKU=G7,'Data Dump'!$Q$6:$Q$37155),0))

confirmed with CTRL+SHIFT+ENTER

but is there a possibility of ties for the highest value for a specific product?