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:

Model	Region	Configuration	Jan-19	Feb-19	Mar-19	Apr-19	May-19
Model 1	Region 1	Configuration 1
Model 7	Region 1	Configuration 8
Model 1	Region 4	Configuration 2
Model 4	Region 4	Configuration 4

<tbody>
</tbody>

Sheet2:

Model	Region	Configuration	Jan-19	Feb-19	Mar-19	Apr-19	May-19
Model 1	Region 4	Configuration 2	2	19	18	18	9
Model 3	Region 2	Configuration 5	0	9	17	5	3
Model 7	Region 1	Configuration 8	0	0	0	0	19
Model 2	Region 6	Configuration 3	7	6	8	7	1

<tbody>
</tbody>

Thank you very much in advance for helping me =)

 

[Kamolga]

Hi,
Normally index match is the most efficient. With so many rows, I would usually go for drop down lists that would show one result only.

For the function, this should do the work

Function ArraySearch(TotArray As Range, vald As Range, Val1 As Range, Val2 As Range)
'TotArray is the total array to look in
'Vald is a cell(range) wich value will be search to define column number
'val1 is the cell.value to be searched in 1st column
'val2 is the cell.value to be searched in 2nd column (offset 0,1)
Dim r As Long 'row
Dim c As Long 'column
'find the right column
For Each cell In TotArray
    If cell.Value = vald.Value Then
        c = cell.Column
        GoTo cFound
    End If
Next cell
'If c = 0 Then Exit Function
cFound:
'Find row meeting conditions
    For Each cell In TotArray
        If (cell.Value = Val1.Value And cell.Offset(, 1).Value = Val2.Value) Then
            r = cell.Row
            GoTo rFound
        End If
    Next cell
    'If r = 0 Then Exit Function
rFound:
    ArraySearch = Worksheets(TotArray.Worksheet.Index).Cells(r, c).Value
End Function

and formula in D2 looks like

=ArraySearch(Sheet2!$A$1:$H$5,D$1,$A2,$B2)

Need to add a third optional argument though

 

[RasGhul]

Hi TG,

Can you use this?

Book1
	A	B	C	D	E	F	G	H
1	Model	Region	Configuration	Jan-19	Feb-19	Mar-19	Apr-19	May-19
2	Model 1	Region 4	Configuration 2	2	19	18	18	9
3	Model 3	Region 2	Configuration 5	0	9	17	5	3
4	Model 7	Region 1	Configuration 8	0	0	0	0	19
5	Model 2	Region 6	Configuration 3	7	6	8	7	1
Sheet2

Book1
	A	B	C	D	E	F	G	H
1	Model	Region	Configuration	Jan-19	Feb-19	Mar-19	Apr-19	May-19
2	Model 1	Region 1	Configuration 1
3	Model 7	Region 1	Configuration 8	0	0	0	0	19
4	Model 1	Region 4	Configuration 2	2	19	18	18	9
5	Model 4	Region 4	Configuration 4
Sheet1

=IFERROR(INDEX(Sheet2!$D$2:$H$5,MATCH(Sheet1!$A2&Sheet1!$B2&Sheet1!$C2,INDEX(Sheet2!$A$2:$A$5&Sheet2!$B$2:$B$5&Sheet2!$C$2:$C$5,),0),MATCH(Sheet1!D$1,Sheet2!$D$1:$H$1,0)),"")

 

[Kamolga]

Hi RasGhul,


Nothing to do with the question but how do you get this nice tables in your answer?

 

[RasGhul]

Hi RasGhul,


Nothing to do with the question but how do you get this nice tables in your answer?

Hi Mate,

Search the forum for the Excel HTML Maker Add in, Mr Excel also did a youtube vid as well showing how to use it;

https://www.youtube.com/watch?v=-_QSkuZ0ppM

 

[Fluff]

Hi RasGhul,


Nothing to do with the question but how do you get this nice tables in your answer?

Have a look here https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[TG2812]

Hi TG,

Can you use this?

Book1
	A	B	C	D	E	F	G	H
1	Model	Region	Configuration	Jan-19	Feb-19	Mar-19	Apr-19	May-19
2	Model 1	Region 4	Configuration 2	2	19	18	18	9
3	Model 3	Region 2	Configuration 5	0	9	17	5	3
4	Model 7	Region 1	Configuration 8	0	0	0	0	19
5	Model 2	Region 6	Configuration 3	7	6	8	7	1
Sheet2

Book1
	A	B	C	D	E	F	G	H
1	Model	Region	Configuration	Jan-19	Feb-19	Mar-19	Apr-19	May-19
2	Model 1	Region 1	Configuration 1
3	Model 7	Region 1	Configuration 8	0	0	0	0	19
4	Model 1	Region 4	Configuration 2	2	19	18	18	9
5	Model 4	Region 4	Configuration 4
Sheet1

=IFERROR(INDEX(Sheet2!$D$2:$H$5,MATCH(Sheet1!$A2&Sheet1!$B2&Sheet1!$C2,INDEX(Sheet2!$A$2:$A$5&Sheet2!$B$2:$B$5&Sheet2!$C$2:$C$5,),0),MATCH(Sheet1!D$1,Sheet2!$D$1:$H$1,0)),"")

Many many thanks for your answer. Much appreciated! Exactly what I needed (both UDF and this index formula)
Do you know how to make dynamic ranges with INDIRECT in the formula you proposed above?

 

[mikerickson]

If you are going for speed, helper columns (concatenate the first three columns) and a VLOOKUP would be a good approach.

 

[TG2812]

Hi TG,

Can you use this?

	A	B	C	D	E	F	G	H
1	Model	Region	Configuration	Jan-19	Feb-19	Mar-19	Apr-19	May-19
2	Model 1	Region 4	Configuration 2	2	19	18	18	9
3	Model 3	Region 2	Configuration 5	0	9	17	5	3
4	Model 7	Region 1	Configuration 8	0	0	0	0	19
5	Model 2	Region 6	Configuration 3	7	6	8	7	1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

	A	B	C	D	E	F	G	H
1	Model	Region	Configuration	Jan-19	Feb-19	Mar-19	Apr-19	May-19
2	Model 1	Region 1	Configuration 1
3	Model 7	Region 1	Configuration 8	0	0	0	0	19
4	Model 1	Region 4	Configuration 2	2	19	18	18	9
5	Model 4	Region 4	Configuration 4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

=IFERROR(INDEX(Sheet2!$D$2:$H$5,MATCH(Sheet1!$A2&Sheet1!$B2&Sheet1!$C2,INDEX(Sheet2!$A$2:$A$5&Sheet2!$B$2:$B$5&Sheet2!$C$2:$C$5,),0),MATCH(Sheet1!D$1,Sheet2!$D$1:$H$1,0)),"")

Many many thanks @RasGhul for your answer. Much appreciated! Exactly what I needed (both UDF and this index formula)
Do you know how to make dynamic ranges with INDIRECT in the formula you proposed above?

 

[RasGhul]

Ok,

I've converted into dynamic named ranges. For speed I wanted to use tables but it changed the Sheet2 date headers into text annoyingly. All of the named ranges use OFFSET, normally we would use indirect to source data between sheets or named ranges that reference sheets.

Hopefully offset doesn't slow down your sheet to much. If you had room to insert a column after model/region/config and concatenate a1&b1&c1 it would speed up index as suggested by mikerickson.

Book1
	A	B	C	D	E	F	G	H
1	Model	Region	Configuration	Jan-19	Feb-19	Mar-19	Apr-19	May-19
2	Model 1	Region 1	Configuration 1
3	Model 7	Region 1	Configuration 8	0	0	0	0	19
4	Model 1	Region 4	Configuration 2	2	19	18	18	9
5	Model 4	Region 4	Configuration 4
Sheet1

Cell Formulas
Range	Formula
D2	=IFERROR(INDEX(datenum,MATCH(Sheet1!$A2&Sheet1!$B2&Sheet1!$C2,INDEX(model1&Region1&Config1,),0),MATCH(Sheet1!D$1,Dates,0)),"")

Named Ranges
Name	Refers To	Cells
Config1	=OFFSET(Sheet2!$C$2,,,COUNTA(Sheet2!$C:$C)-1)
datenum	=OFFSET(Sheet2!$D$2,,,COUNT(Sheet2!$D$2:$D$5),COUNTA(Sheet2!$D$2:$O$2))
Dates	=OFFSET(Sheet2!$D$1,,,,COUNT(Sheet2!$1:$1))
model1	=OFFSET(Sheet2!$A$2,,,COUNTA(Sheet2!$A:$A)-1)
Region1	=OFFSET(Sheet2!$B$2,,,COUNTA(Sheet2!$B:$B)-1)