Finding a text in a list against a specific item and returning the text in new sheet

Hi, i was wondering if someone can help me with something. I have a template with a list of data from store 1 to store 78 and i want to be able to bring accross into another sheet. So far with the numbers i have been fine and have used the following formula to bring this accross:

=SUMIF('Weekly Retail Sales'!$B$6:$B$93,"Store 1",'Weekly Retail Sales'!D$6:D$93)

Is there something similar that will bring accross text when "Store 1" is found in the range "$B$6:$B$93"?

All of the text is in one column and the range would be $Y$6:$Y$93 if that helps.

Thanks in advance.

Why not use a Pivot table based on a dynamic named range?

What i'm doing is setting up a template for another team so although i know how to do this, they dont and i'm trying to make the process they follow more efficient.

Ideally i'd like a formula like what i have used for the numbers if possible.

This sounds like a look up and match scenario.
If a cell in column-B contains "Store 1" thenyou want to return the corresponding value on the same row but from column-Y. Correct?

Take a look at the attached. see sheet1 (data) and sheet2 (lookup formula).
Is this what you want?

This looks like what i'm after but i cant get it to work. The file is below, can you see if it works in there?

Sheet 1 is where the original data is going to be input to and sheet 2 will be in a slightly different format to sheet 1 but this is where i need the text to appear. The numbers already work, its just the text in column Y on Sheet 1 that i want to be able to transfer to column z on sheet 2.

This is pretty much what I showed in my example workbook, you only need to modify the formula for your cell references.

Adjust the range reference for sheet1 as required.
=VLOOKUP(C6,Sheet1!$B$6:$Z$10,24,FALSE)

Thanks for your help with this. I went with a slightly different version of your formula but it works (which is the most important thing).

=VLOOKUP(C6,Sheet1!$B$6:$Z$10,24,0)

Just did a conditional format on the 0 to hide it and it all systems go.