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

 

[Special-K99]

Try replacing

'Raw Data'!$F$6:$G$10=$A$3

with

('Raw Data'!$F$6:$G$10=$B$3+'Raw Data'!$F$6:$G$10=$C$3+'Raw Data'!$F$6:$G$10=$D$3)

Note, extra brackets at beginning and end

 

[amyj22x3]

Thanks for replying.

Didn't get an error, but everything went blank instead: https://ezgif.com/image-to-datauri/ezgif-2-d3230893df.jpg

 

[amyj22x3]

Also, it needs to be an OR or AND/OR because the others cells may or may not contain text.

 

[Special-K99]

The + in the formula is the equivalent of OR

All I'm getting on that link is "Image to data URl converter"
and its asking me to upload (???) an image

 

[Aladin Akyurek]

What is the range which must equal to B1, C1, D1, or E1?

'Raw Data'!$F$6:$G$10,

'Raw Data'!$F$6:$F$10, or

'Raw Data'!$G$6:$G$10?​

​

​

 

[amyj22x3]

Can you see the first images I linked? I did it the same way, so now I'm wondering it I did it wrong in the beginning.

Then ranged being referenced is 'Raw Data'!$F$6:$G$10

I didn't know + equaled OR, thanks.

 

[amyj22x3]

Let me try again.

https://ezgif.com/image-to-datauri/ezgif-1-57641969f4.jpg

 

[Aladin Akyurek]

Can you see the first images I linked? I did it the same way, so now I'm wondering it I did it wrong in the beginning.

Then ranged being referenced is 'Raw Data'!$F$6:$G$10

I didn't know + equaled OR, thanks.

The question was: Where does B1 occur if it occurs at all? In

'Raw Data'!$F$6:$F$10 or

'Raw Data'!$G$6:$G$10 or in both?
​

​

<strike></strike>

 

[amyj22x3]

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