Find/match values in 5 consecutive cells in one row with the same 5 values in a different range.

[coveredinbutter]

Looking for some guidance

Not sure if I explained in correctly in the title:

For the values in cells A1, B1,C1,D1,E1, F1, G1. I want to find the row in range J1:P50 where every value in the row matches the values in A1:G1.

Any help is appreciated.

 

[RasGhul]

Maybe this?

Find-match values in 5 consecutive cells in one row_coveredinbutter.xlsx
	A	B	C	D	E	F	G			J	K	L	M	N	O	P
1	1	2	3	4	5	6	7			10	30	50	70	90	110	130
2										11	31	51	71	91	111	131
3	Row									12	32	52	72	92	112	132
4	7									13	33	53	73	93	113	133
5										14	34	54	74	94	114	134
6										15	35	55	75	95	115	135
7										1	2	3	4	5	6	7
8										17	37	57	77	97	117	137
9										18	38	58	78	98	118	138
10										19	39	59	79	99	119	139
11										20	40	60	80	100	120	140
12										21	41	61	81	101	121	141
13										22	42	62	82	102	122	142
14										23	43	63	83	103	123	143
15										24	44	64	84	104	124	144
16										25	45	65	85	105	125	145
17										26	46	66	86	106	126	146
Sheet1

Cell Formulas
Range	Formula
K1:P6,K8:P17	K1	=J1+20
A4	A4	=SUMPRODUCT(($J$1:$J$50=$A$1)*($K$1:$K$50=$B$1)*($L$1:$L$50=$C$1)*($M$1:$M$50=$D$1)*($N$1:$N$50=$E$1)*($O$1:$O$50=$F$1)*($P$1:$P$50=$G$1)*(ROW($J$1:$J$50)-ROW($J$1)+1))

 

[johnnyL]

Here is a vba macro code approach:

Sub FindMatch()
'
    Dim ColumnCounter   As Long
    Dim FirstRowToLoad  As Long, MaxRowToLoad   As Long
    Dim ArrayAtoG       As Variant, ArrayJtoP   As Variant
'
    FirstRowToLoad = 1                                                              ' <--- Set this to first row to load
    MaxRowToLoad = 50                                                               ' <--- Set this to last row to load
'
    ArrayAtoG = Range("A1:G1")                                                      ' Load A1:G1 to 2D One based array ... RC ... 1,1 thru 1,7
'
    ArrayJtoP = Range("J" & FirstRowToLoad & ":P" & MaxRowToLoad)                   ' Load J:P range into 2D One based ArrayJtoP ... RC
'
    ColumnCounter = 1                                                               ' Initialize ColumnCounter
'
    For ArrayJtoP_Row = FirstRowToLoad To MaxRowToLoad                              ' Loop to loop through ArrayJtoP
        If ArrayJtoP(ArrayJtoP_Row, ColumnCounter) = ArrayAtoG(1, 1) And _
            ArrayJtoP(ArrayJtoP_Row, ColumnCounter + 1) = ArrayAtoG(1, 2) And _
            ArrayJtoP(ArrayJtoP_Row, ColumnCounter + 2) = ArrayAtoG(1, 3) And _
            ArrayJtoP(ArrayJtoP_Row, ColumnCounter + 3) = ArrayAtoG(1, 4) And _
            ArrayJtoP(ArrayJtoP_Row, ColumnCounter + 4) = ArrayAtoG(1, 5) And _
            ArrayJtoP(ArrayJtoP_Row, ColumnCounter + 5) = ArrayAtoG(1, 6) And _
            ArrayJtoP(ArrayJtoP_Row, ColumnCounter + 6) = ArrayAtoG(1, 7) Then      '   If all values match then ...
                MsgBox "Match found in row " & ArrayJtoP_Row                        '       Display Message box indicating the matching row
                Exit Sub                                                            '       Exit this sub
        End If
    Next                                                                            ' Loop back
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

Do the numbers have to be in the exact same order?
For example, would it still be row 7 with this sample data?

21 12 21.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	1	2	3	4	5	6	7			10	30	50	70	90	110	130
2										11	31	51	71	91	111	131
3	Row									12	32	52	72	92	112	132
4										13	33	53	73	93	113	133
5										14	34	54	74	94	114	134
6										15	35	55	75	95	115	135
7										6	1	7	4	3	2	5
8										17	37	57	77	97	117	137
Sheet2 (2)

 

[coveredinbutter]

Thanks everyone. I was trying to do a lookup with the 7 values to another 7 values and I think I was making it too complicated. I was able to make it work with an xlookup using this format.

=XLOOKUP(val1&val2&val3,rng1&rng2&rng3,results)

Thanks for all the help.