Index/Match with OR

[brunette]

Hi Everyone,

Using Excel 2010:

On Sheet 1 I have a product name (column C - populated) and product provider (column D - unpopulated).

I need to get the corresponding product provider for the product name.

The thing is that this information is somewhere in column C of either sheet 2 OR sheet 3 OR sheet 4 OR sheet 5 (I cannot change this).
I am matching the product name against the the corresponding name listed somewhere on column A of sheet 2 OR sheet 3 OR sheet 4 OR sheet 5.

But, of course, my formula errors.

I would normally do a simple index/match if this info was somewhere on another, single sheet, but I'm having difficulty in attempting to combine index/match with OR and trying to put everything onto one formula.

I've tried the following, which does not work:

=INDEX(OR(SHEET2!C:C;SHEET3!C:C;SHEET4!C:C;SHEET5!C:C);MATCH([@[PRODUCT NAME]];OR(SHEET2!A:A;SHEET3!A:A;SHEET4!A:A;SHEET5!A:A);0)))

PS1: Yes my sheets are formatted as tables
PS2: My regional settings require a ; as separators

Clearly I'm attempting the impossible...any ideas or suggestions?

Many thanks and wishing everyone Happy Holidays,
Ana

 

[Gerald Higgins]

Hmm.

If you really can't re-structure your data, perhaps something like

=iferror(index(sheet2!C:C,match([@[product name]];sheet2!A:A,0)),iferror(index(sheet3!C:C,match([@product name]];sheet3!A:A,0)). . . . and so on.

Not tested, but this might get you started.

 

[brunette]

Hi and thanks for your feedback.

I tried and it doesn't work. However, I am thinking that it might have something to do with the fact that it's mixing a named range with a normal data list. The reason I say this is because I tested a simple index/match formula and it doesn't like the fact that I have a name in there either. So...back to the drawing board for me. Going to keep at it.

Any other suggestions/ideas are welcome!

Thanks again,
Ana

 

[Gerald Higgins]

Even if you can't change the fact that the data is spread across 4 sheets, are you able to create a single sheet that repeats all your data ?
If yes, do that and just apply the Index / Match to that sheet.

 

[brunette]

Hi,

Looking at things I see I will probably have to consider taking this and just restructuring it into something more cohesive, yes. However, it'll be a monster job as there are about a dozen other files that all feed off this one and they will all be impacted as well, so I'll have to go and redo those also.

All of this because my current monster DB file is now too large for Excel 2010 to handle and too piddly for Access to be bothered with... *sigh*

Thanks for your help, though. I really appreciate it. Gonna keep slogging at it
Ana

 

[Gerald Higgins]

Good luck !

If it really is too large for Excel then it's definitely not too small for Access

 

[brunette]

I assume it's getting too complicated for Excel...it keeps generating errors and giving me all sorts of headaches...it's only a 60MB file...

plus...there's the fact that I know Excel but Access......uhm....*runs*