Return the top 3 products using lookup formula

[Queenie Balb]

I have a long spreadsheet with the cities and the top 3 products already sorted:
City Top 3 products
Madras BL 25ml
Madras FC 50 ml
Madras EO 25ml
Toronto BB
Toronto NC
Toronto Lip balm
Singapore Shampoo
Singapore Conditioner
Singapore Lipstick
Rio Hand cream
Rio Nail polish
Rio Frying pan

In another tab, I want to vlookup the city (from the dropdown list) and get the top 3 products. How do I do that. Vlookup only returns the top value and Index & match haven't worked for me. Maybe I am missing a critical step.

[cbatrody]

Hi,

Welcome to the Forum.

You would get better help if you attach a sample workbook with enough data to demonstrate your requirement. Make sure your desired results are shown, mock them up manually if necessary. Remember to desensitize the file by removing all confidential information before upload!

See the following URL for help on how to upload a file.
http://www.excelforum.com/members/da...ch-a-file.html

[Queenie Balb]

File is attached. Thank you!

[cbatrody]

Hi,

Try the following array formula in B7:

=INDEX(data!C$1:C$17,SMALL(IF(data!B$1:B$17=$B$3,ROW(data!C$1:C$17)),ROWS(A$1:A1)))

Please note that this needs to be confirmed by pressing CTRL+SHIFT+ENTER

drag this down the cells, see the attached file.

[shukla.ankur281190]

Or you can also try below formula for avoiding CTRL+SHIFT+ENTER

B7=INDEX(data!$C$1:$C$17,AGGREGATE(15,6,ROW(data!$B$1:$B$17)/(data!$B$1:$B$17=Display!$B$3),ROW(1:1)))

Please Login or Register  to view this content.

[FlameRetired]

This is a non-array formula.

Please Login or Register  to view this content.

[vlady]

Another one. Array Entered

Please Login or Register  to view this content.

TORONTO Exotic oil 60
Toronto Body butter 89
Toronto Night cream 89
Toronto Lip balm 60

[Queenie Balb]

Thank you all. It is solved now!

[shukla.ankur281190]

Glad to hear it !!!! please mark as it solved and thanks for feedback by adding rep to us !!!!!

[Queenie Balb]

Hi guys,
I have detected an unforeseen problem. Please open the file MOCK UP JOURNEY PLAN and look at the cells colored in yellow.

In tab Journey Plan, I need to return the top 5 products in range H19:L23.
This info is in tab Categ, range Q:U.
The formula works alright, but when I come to Polynesian spa, it only has 3 products. These 3 products are returned into tab Journey Plan range H19, H20 and H21. However, on H22 and H23, the formula picks up the products from the following Spa (Champs Elisees).
I need the range H22:H23 to show blank.
Anyway, I thought this would easy to fix because these fields are driven by cell B1, but I am having trouble with this.
Can you please help? The file is attached.
Thanks a lot.