Looking to modify an index formula to check a second variable

0
votes

I have a spreadsheet with the a column for Location and a column for channel. It's organized as follows:

Column A - Column B
LocationA - Channel 1
LocationA - Channel 2
LocationA - Channel 4
LocationB - Channel 2
LocationB - Channel 3

I'm trying to populate a second sheet thats organized as follows:

        Channel 1   Channel 2  Channel 3   Channel 4

LocationA

LocationB

LocationC

I'm trying to add text to each channel column if the Location contains that channel.

I've tried to use this formula:

=INDEX(B:B,MATCH(C2,A:A,0))

But it doesnt confirm that the result matches the Channel column. If I manipulate the main data set and filter by Channel first, it works, but I'm hoping there is a way to avoid the front end work to make it more scalable.

2
excelexcel-formula

2 Answers

1
votes

Assuming you data starting from A2 through B6

form D2 to D4 (Location1,Location2,Location3)

form E1 to I1 (Channel 1,Channel 2,Channel 3,Channel 4)

Try this Array Formula (Ctrl+Shift+Enter) in E2

and drag down and to right

=IF(ISNUMBER(MATCH($D2&E$1,$A$2:$A$6&$B$2:$B$6,0)),"Ok","")
0
votes

Judging from your INDEX+MATCH formula, you want to return the corresponding Channel Name in the new table for each matched Location and Channel Name.

One way of doing that is to use AGGREGATE to find out the position of the Channel Name in the Channel Name range, and then use INDEX to return the actual name.

Presume you have named the following:

  1. Locations: all location names in Column A, eg. A1:A6;
  2. Channels: all channel names in Column B, eg. B1:B6.

Here it the formula presume your new table range is D1:H4:

=IFERROR(INDEX(Channels,AGGREGATE(14,7,ROW(Channels)/(Locations=$D2)/(Channels=E$1),1)),"")

Drag it across board to apply to the rest.

Solution

Let me know if you have any questions. Cheers :)