Need to add OR statement to Index(SMALL(ROWS formula

[amyj22x3]

My initial problem was this:

Ihave a coworker that maintains a spreadsheet comprised of several tabs, thefirst is the main and contains all the information about each business partnerwith columns showing their 1st choice wholesaler, 2nd choice, 3rd choice and soon. And then there's a tab for each of the wholesalers listed on the main page.I've been asked to automate each of the wholesaler tabs to automaticallypopulate with the partner name and contact information regardless of what orderthat wholesaler was chosen. The problem with index(match is that it picks upthe first instance of a partner and repeats that name each time that partnershows up with a different wholesaler name.

https://ezgif.com/image-to-datauri/ezgif-2-47b75ef58d.jpg

This formula works for that problem: =IFERROR(INDEX('Raw Data'!$A:$A,SMALL(IF('Raw Data'!$F$6:$G$10=$A$3,ROW('Raw Data'!$F$6:$F$10)),ROWS(A$5:A5))),"")

New problem: One of the wholesalers has 3 names, so each partner can call them by a different name, but we want to collect all under the main name which is ABC. So for this formula to work I have to create several tables for the different names of the same wholesaler. So instead of referencing one cell for each wholesaler, I know need to write the formulas in Column A with and OR statement to look at multiple cells like B1, C1 or D1.

https://ezgif.com/image-to-datauri/ezgif-2-18082a54ef.jpg

Bottom line: I need to edit my formula to make each table look at cells B1, C1, D1 and E1

 

[Aladin Akyurek]

It all references the same cells: 'Raw Data'!$F$6:$G$10 each time.

I don't think my question is clear.

A1: jon
A2: linda
B1: karen
B2: jon

As jon can occur in A1:A2 as well as B1:B2, I can now say something like when A1:B2 = jon, then do X.

But if jon cannot occur in either A1:A2 or in B1:B2 or in both ranges, I should avoid saying when A1:B2 = jon, do X.

Does this help understand my question of the post #6 ?