UDF with horizontal and vertical search array and multiple argument

T

TG2812

Board Regular
Joined
Apr 15, 2015
Messages
192
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 =)
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
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
Code: 
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

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

Need to add a third optional argument though
 
Upvote 0
Hi TG,

Can you use this?


Book1
ABCDEFGH
1ModelRegionConfigurationJan-19Feb-19Mar-19Apr-19May-19
2Model 1Region 4Configuration 221918189
3Model 3Region 2Configuration 5091753
4Model 7Region 1Configuration 8000019
5Model 2Region 6Configuration 376871
Sheet2



Book1
ABCDEFGH
1ModelRegionConfigurationJan-19Feb-19Mar-19Apr-19May-19
2Model 1Region 1Configuration 1
3Model 7Region 1Configuration 8000019
4Model 1Region 4Configuration 221918189
5Model 4Region 4Configuration 4
Sheet1



Code: 
=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)),"")
 
Upvote 0
RasGhul said:
Hi TG,

Can you use this?


Book1
ABCDEFGH
1ModelRegionConfigurationJan-19Feb-19Mar-19Apr-19May-19
2Model 1Region 4Configuration 221918189
3Model 3Region 2Configuration 5091753
4Model 7Region 1Configuration 8000019
5Model 2Region 6Configuration 376871
Sheet2



Book1
ABCDEFGH
1ModelRegionConfigurationJan-19Feb-19Mar-19Apr-19May-19
2Model 1Region 1Configuration 1
3Model 7Region 1Configuration 8000019
4Model 1Region 4Configuration 221918189
5Model 4Region 4Configuration 4
Sheet1



Code: 
=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)),"")
Click to expand...

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?
 
Upvote 0
If you are going for speed, helper columns (concatenate the first three columns) and a VLOOKUP would be a good approach.
 
Upvote 0
RasGhul said:
Hi TG,

Can you use this?

ABCDEFGH
Model 1Region 4Configuration 2
Model 3Region 2Configuration 5
Model 7Region 1Configuration 8
Model 2Region 6Configuration 3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4472C4]#4472C4[/URL] "]Model[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4472C4]#4472C4[/URL] "]Region[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4472C4]#4472C4[/URL] "]Configuration[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4472C4]#4472C4[/URL] , align: right"]Jan-19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4472C4]#4472C4[/URL] , align: right"]Feb-19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4472C4]#4472C4[/URL] , align: right"]Mar-19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4472C4]#4472C4[/URL] , align: right"]Apr-19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4472C4]#4472C4[/URL] , align: right"]May-19[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]

</tbody>
Sheet2​



ABCDEFGH
Model 1Region 1Configuration 1
Model 7Region 1Configuration 8
Model 1Region 4Configuration 2
Model 4Region 4Configuration 4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ED7D31]#ED7D31[/URL] "]Model[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ED7D31]#ED7D31[/URL] "]Region[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ED7D31]#ED7D31[/URL] "]Configuration[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ED7D31]#ED7D31[/URL] , align: right"]Jan-19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ED7D31]#ED7D31[/URL] , align: right"]Feb-19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ED7D31]#ED7D31[/URL] , align: right"]Mar-19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ED7D31]#ED7D31[/URL] , align: right"]Apr-19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ED7D31]#ED7D31[/URL] , align: right"]May-19[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]5[/TD]

</tbody>
Sheet1​




Code: 
=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)),"")
Click to expand...



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?
 
Upvote 0
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
ABCDEFGH
1ModelRegionConfigurationJan-19Feb-19Mar-19Apr-19May-19
2Model 1Region 1Configuration 1 
3Model 7Region 1Configuration 8000019
4Model 1Region 4Configuration 221918189
5Model 4Region 4Configuration 4
Sheet1
Cell Formulas
RangeFormula
D2=IFERROR(INDEX(datenum,MATCH(Sheet1!$A2&Sheet1!$B2&Sheet1!$C2,INDEX(model1&Region1&Config1,),0),MATCH(Sheet1!D$1,Dates,0)),"")
Named Ranges
NameRefers ToCells
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)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA to add to data model when creating Power Query tables`
Replies
3
Views
577
Raddle
R
Nlhicks
  • Solved
Take Region, Find Right Sheet, Find Right Sub, Copy Update to Right Row
Replies
8
Views
241
RajeshKumar1313
R
F
  • Solved
Formula - Sumif(s)? Sumproduct? etc.
2
Replies
16
Views
278
Fluff
Fluff
C
  • Question Question
Subscriber numbers with different payment plans
Replies
2
Views
78
CTMom
C
ExcelNewbie2020
  • Solved
Lookup duplicate value with multiple criteria
Replies
9
Views
238
Fluff
Fluff

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top