MistaMista
New Member
- Joined
- May 18, 2021
- Messages
- 14
- Office Version
- 2019
- Platform
- Windows
- MacOS
Hello!
I have a hard time finding the solution to this problem. I am not allowed to make any changes in the structure / format in sheet. Keep in mind, the two tables is in two different sheets (extremely simplified version, but formula is to be in Sheet 1, Column DISTRIBUTOR A1:A214)
SHEET 1: Bottleshop
So I ran Solver, and based on minimizing objective cell, and we are now left with a BINARY table with the 20 different cities in ROW B2:U2, and the six different distributors in column A3:A6
See below for example. Each city are only provided by one distributor. Each city can have multiple bottleshops, but they will be provided by the same. Hence 214 shops, 20 cities, 6 distributors.
Sheet 2: DISTRIBUTOR
So, my brain is cooking. How do i find where City in sheet one, matches city in Sheet two, but is equal to 1, so the return value is the distributor in column A?
I've tried index, match, ifs, or, but i can't figure this one out. I have Microsoft 2019, XLOOKUP or XMATCH doesnt work. Only Lookup, Vlookup, Hlookup, INDEX, MATCH.
For example, in sheet one, A5 Would be Popeye, because the binary table shows 1 under Berlin - Popeye
I have a hard time finding the solution to this problem. I am not allowed to make any changes in the structure / format in sheet. Keep in mind, the two tables is in two different sheets (extremely simplified version, but formula is to be in Sheet 1, Column DISTRIBUTOR A1:A214)
SHEET 1: Bottleshop
DISTRIBUTOR | CITY |
???? formula to match the City name -> with binary table in sheet 2 | New York |
London | |
London | |
Berlin | |
Ipswich | |
Berlin | |
.. goes all the way down. to A214, in total there is 20 different cities, and only 6 different distributors. |
So I ran Solver, and based on minimizing objective cell, and we are now left with a BINARY table with the 20 different cities in ROW B2:U2, and the six different distributors in column A3:A6
See below for example. Each city are only provided by one distributor. Each city can have multiple bottleshops, but they will be provided by the same. Hence 214 shops, 20 cities, 6 distributors.
Sheet 2: DISTRIBUTOR
Berlin | Ipswich | London (keeps going to U2 with 20 unique cities.) | |
Max | 0 | 1 | 0 |
Oscar | 0 | 0 | 1 |
Popeye | 1 | 0 | 0 |
Champis | 0 | 0 | 0 |
North | 0 | 0 | 0 |
So, my brain is cooking. How do i find where City in sheet one, matches city in Sheet two, but is equal to 1, so the return value is the distributor in column A?
I've tried index, match, ifs, or, but i can't figure this one out. I have Microsoft 2019, XLOOKUP or XMATCH doesnt work. Only Lookup, Vlookup, Hlookup, INDEX, MATCH.
For example, in sheet one, A5 Would be Popeye, because the binary table shows 1 under Berlin - Popeye