UDF with horizontal and vertical search array and multiple argument

[TG2812]

Hello All,

I need your help.
I'm working on a file where I need to search horizontally and vertically with multiple conditions.

What i did try before coming asking for help here?
a) I tried an array formula which worked. However my file contains more than 50,000 rows. This makes the file way too heavy.
b) I tried a simple index match formula with an helper column which worked as well. However, this does not really help for the future use of my file..

The only solution left if to create a UDF. I browsed around but I could not find any UDF that could really suit my needs and I'm a bit of a beginner when it comes to creating UDF.

I have included an example of what I'm looking for.
In a nutshell;

1) I have two worksheets (Sheet 1 & Sheet2).
2) I need to lookup value of the Sheet2 into Sheet 1 based on the correct date (horizontal array), correct model (vertical array), correct region (vertical array), correct configuration (vertical array)

Important note: the UDF needs to have more than 3 conditions as I may face this scenario in the future.

Sheet1:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Model[/TD]
[TD]Region[/TD]
[TD]Configuration[/TD]
[TD]Jan-19[/TD]
[TD]Feb-19[/TD]
[TD]Mar-19[/TD]
[TD]Apr-19 [/TD]
[TD]May-19[/TD]
[/TR]
[TR]
[TD]Model 1[/TD]
[TD]Region 1[/TD]
[TD]Configuration 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Model 7[/TD]
[TD]Region 1[/TD]
[TD]Configuration 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Model 1[/TD]
[TD]Region 4[/TD]
[TD]Configuration 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Model 4[/TD]
[TD]Region 4[/TD]
[TD]Configuration 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet2:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Model[/TD]
[TD]Region[/TD]
[TD]Configuration[/TD]
[TD]Jan-19[/TD]
[TD]Feb-19[/TD]
[TD]Mar-19[/TD]
[TD]Apr-19[/TD]
[TD]May-19[/TD]
[/TR]
[TR]
[TD]Model 1[/TD]
[TD]Region 4[/TD]
[TD]Configuration 2[/TD]
[TD]2[/TD]
[TD]19[/TD]
[TD]18[/TD]
[TD]18[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Model 3[/TD]
[TD]Region 2[/TD]
[TD]Configuration 5[/TD]
[TD]0[/TD]
[TD]9[/TD]
[TD]17[/TD]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Model 7[/TD]
[TD]Region 1[/TD]
[TD]Configuration 8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]Model 2[/TD]
[TD]Region 6[/TD]
[TD]Configuration 3[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Thank you very much in advance for helping me =)

 

[mikerickson]

Those Offsets are slowing things down.

=Sheet2!$C$2:INDEX(Sheet2!$C:C,COUNTA(Sheet2!$C:$C), 1)

is a non volatile definition of Config1.

The other OFFSET can be replaced by similar formulas.

 

[TG2812]

Thank you all, it works like a charm!