Return Multiple Values

Ameryjackson

New Member
Joined
Oct 15, 2017
Messages
4
Hello, I am having great difficulty in producing table 3 from tables 1 and 2. I have had some success using INDEX and MATCH functions, however they are limited to one result and will only return the first result. Any help is greatly appreciated. Thanks in advance.


[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Meters Total[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]
Table 1

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Depth From[/TD]
[TD]Depth To[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]Quartz[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]Bones[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]Bones[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]Roots[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]Quartz[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]Quartz[/TD]
[/TR]
</tbody>[/TABLE]
Table 2

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Depth From[/TD]
[TD]Depth To[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]Quartz[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]Quartz[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]Quartz[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]Bones[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]Bones[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]Bones[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]Roots[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]Roots[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]Roots[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]Quartz[/TD]
[/TR]
</tbody>[/TABLE]
Table 3
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
If VBA is acceptable then try this:-
With Table 1 starting "A1", and Table2 starting "D1" then the results Start "I1".
Code:
[COLOR="Navy"]Sub[/COLOR] MG16Oct38
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ray(), n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
ReDim Preserve Ray(1 To 4, 1 To 1)
Ray(1, 1) = "ID": Ray(2, 1) = "Depth From": Ray(3, 1) = "Depth To": Ray(4, 1) = "Code"
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] n = 1 To Dn.Offset(, 1)
        c = c + 1
        ReDim Preserve Ray(1 To 4, 1 To c)
        Ray(1, c) = Dn.Value
        Ray(2, c) = n - 1
        Ray(3, c) = n
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]For[/COLOR] Ac = 1 To Dn.Offset(, 2).Value - Dn.Offset(, 1).Value
                txt = Dn.Value & Dn.Offset(, 1).Value + Ac - 1 & Dn.Offset(, 1).Value + Ac
            
                    [COLOR="Navy"]If[/COLOR] Not .exists(txt) [COLOR="Navy"]Then[/COLOR]
                         .Add txt, Dn.Offset(, 3).Value
                    [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Ac
        [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 2)
    txt = Ray(1, n) & Ray(2, n) & Ray(3, n)
        [COLOR="Navy"]If[/COLOR] .exists(txt) [COLOR="Navy"]Then[/COLOR] Ray(4, n) = .Item(txt)
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Range("I1").Resize(c, 4)
    .Value = Application.Transpose(Ray)
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Hi @ Ameryjackson

You need an array formula to get the codes in Table3

Assuming you Table2 starts from Cells A1 (Headings in row1)

Put this formula in the Table 3 Code Column and PRESS CTRL+SHIFT+ENTER

Code:
=IFERROR(INDEX($D$2:$D$7,MATCH(1,(A17=$A$2:$A$7)*(B17>=$B$2:$B$7)*(C17<=$C$2:$C$7),0),1),"")

DONT MISS CTRL+SHIFT+ENTER
 
Upvote 0
2rzn3h6
 
Last edited:
Upvote 0
A solution using formulas


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A
[/TD]
[TD="bgcolor: #DCE6F1"]
B
[/TD]
[TD="bgcolor: #DCE6F1"]
C
[/TD]
[TD="bgcolor: #DCE6F1"]
D
[/TD]
[TD="bgcolor: #DCE6F1"]
E
[/TD]
[TD="bgcolor: #DCE6F1"]
F
[/TD]
[TD="bgcolor: #DCE6F1"]
G
[/TD]
[TD="bgcolor: #DCE6F1"]
H
[/TD]
[TD="bgcolor: #DCE6F1"]
I
[/TD]
[TD="bgcolor: #DCE6F1"]
J
[/TD]
[TD="bgcolor: #DCE6F1"]
K
[/TD]
[TD="bgcolor: #DCE6F1"]
L
[/TD]
[TD="bgcolor: #DCE6F1"]
M
[/TD]
[TD="bgcolor: #DCE6F1"]
N
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1
[/TD]
[TD]
ID​
[/TD]
[TD]
Meters Total​
[/TD]
[TD][/TD]
[TD]
ID​
[/TD]
[TD]
Depth From​
[/TD]
[TD]
Depth To​
[/TD]
[TD]
Code​
[/TD]
[TD][/TD]
[TD]
ID​
[/TD]
[TD]
Depth From​
[/TD]
[TD]
Depth To​
[/TD]
[TD]
Code​
[/TD]
[TD][/TD]
[TD]
Helper​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2
[/TD]
[TD]
A​
[/TD]
[TD]
5​
[/TD]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
Quartz​
[/TD]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3
[/TD]
[TD]
B​
[/TD]
[TD]
8​
[/TD]
[TD][/TD]
[TD]
B​
[/TD]
[TD]
4​
[/TD]
[TD]
6​
[/TD]
[TD]
Bones​
[/TD]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
Quartz​
[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4
[/TD]
[TD]
C​
[/TD]
[TD]
10​
[/TD]
[TD][/TD]
[TD]
B​
[/TD]
[TD]
7​
[/TD]
[TD]
8​
[/TD]
[TD]
Bones​
[/TD]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[TD]
Quartz​
[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]
13​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C​
[/TD]
[TD]
4​
[/TD]
[TD]
7​
[/TD]
[TD]
Roots​
[/TD]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
3​
[/TD]
[TD]
4​
[/TD]
[TD]
Quartz​
[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]
23​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
2​
[/TD]
[TD]
4​
[/TD]
[TD]
Quartz​
[/TD]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
4​
[/TD]
[TD]
5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C​
[/TD]
[TD]
9​
[/TD]
[TD]
10​
[/TD]
[TD]
Quartz​
[/TD]
[TD][/TD]
[TD]
B​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
B​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
B​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
B​
[/TD]
[TD]
3​
[/TD]
[TD]
4​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
B​
[/TD]
[TD]
4​
[/TD]
[TD]
5​
[/TD]
[TD]
Bones​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
B​
[/TD]
[TD]
5​
[/TD]
[TD]
6​
[/TD]
[TD]
Bones​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
B​
[/TD]
[TD]
6​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
B​
[/TD]
[TD]
7​
[/TD]
[TD]
8​
[/TD]
[TD]
Bones​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
17
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
18
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C​
[/TD]
[TD]
3​
[/TD]
[TD]
4​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
19
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C​
[/TD]
[TD]
4​
[/TD]
[TD]
5​
[/TD]
[TD]
Roots​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C​
[/TD]
[TD]
5​
[/TD]
[TD]
6​
[/TD]
[TD]
Roots​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
21
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C​
[/TD]
[TD]
6​
[/TD]
[TD]
7​
[/TD]
[TD]
Roots​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
22
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C​
[/TD]
[TD]
7​
[/TD]
[TD]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
23
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C​
[/TD]
[TD]
8​
[/TD]
[TD]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
24
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
C​
[/TD]
[TD]
9​
[/TD]
[TD]
10​
[/TD]
[TD]
Quartz​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
25
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Helper column (to make the formulas simpler) - gray area
Formula in N2 copied down
=SUM(B$1:B1)

Formula in I2 copied down
=IF(ROWS(I$2:I2)>N$5,"",INDEX(A$2:A$4,MATCH(ROWS(I$2:I2)-1,N$2:N$4)))

Formula in J2 copied down
=IF(I2="","",COUNTIF(I$2:I2,I2)-1)

Formula in K2 copied down
=IF(J2="","",J2+1)

Formula in L2 copied down
=IF(J2="","",IF(COUNTIFS(D$2:D$7,I2,E$2:E$7,"<="&J2,F$2:F$7,">="&K2),INDEX(G$2:G$7,MATCH(I2,D$2:D$7,0)),""))

Hope this helps

M.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

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