Multiple answers in Lookups

**scmuddy** · 02-19-2013, 09:08 AM

Hi Guys I need some help with LOOKUPS please.
I know how to get a result from creating a lookup function, but I need to go deeper into the formula.

I have a warehouse that has multiple locations, I need to know a formula to get a different result from the same location i.e:

Location A-01-1 Products in location FF0111W, FS107W, GG0111W etc.

I have approximately 1200 pallet locations.
Each location can hold up to 30 different cartons

Example of result I wish to have:
A-01-1 FF0111W
A-01-1 FS107W
A-01-1 GG0111W

The result I get currently is:
A-01-1 FF0111W
A-01-1 FF0111W
A-01-1 FF0111W

Please help
Regards
Steve

**Jacc** · 02-19-2013, 09:36 AM

Check out the two last posts 4 and 5 here:
http://www.excelforum.com/excel-form...xt-record.html

Seems to me it's basically the same problem. It can be expanded to include multiple criteria.

**Cheeky Charlie** · 02-19-2013, 09:36 AM

I understand why you would see this as a more advanced lookup, but the best solution for this scenario is (probably) a pivot table. There is an excellent website called contextures which will guide you through from absolute basics to advanced use.

**scmuddy** · 02-19-2013, 09:40 AM

I have attached a sample from my report.
Hope this helps you guys out.

Regards
Steve

**benishiryo** · 02-19-2013, 10:00 AM

maybe an array formula like this in B2:

Formula:

Please Login or Register  to view this content.

you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER. When done correctly, you should see curly brackets surrounding it.

**Cheeky Charlie** · 02-19-2013, 10:01 AM

I *really* think you should take the time to learn pivot tables. it took about 12 seconds to create a pivot which achieves your output and it's much easier to manage.