Advanced INDEX / VLOOKUP / MATCH (??) With row, column AND binary table

MistaMista

New Member
Joined
May 18, 2021
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
  2. 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
DISTRIBUTOR
CITY
???? formula to match the City name -> with binary table in sheet 2New 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 010
Oscar001
Popeye100
Champis000
North000

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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
72,989
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=INDEX(Sheet2!A3:A100,MATCH(1,INDEX(Sheet2!B3:U100,0,MATCH(B2,Sheet2!B2:U2,0)),0))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
72,989
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,492
Messages
5,832,008
Members
430,103
Latest member
BIGGAZ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top