FIND EXACT MATCH AND RETURN ROW NUMBER WHERE FOUND

Question

Hello everybody

here is a thing kinda hard to solve please

file attached with expected results

what is needed that to find exact match of four numbers at left in the table on right side and return number of the row where it was found

size of table or numbers might differ

thank you in advance

Answer 1

It already returns the 1st matching row number.

Regards,

Amit Tandon

www.excelanytime.com

Answer 2

Hi Mike

Perhaps someone else could find a formula solution for your problem.

I chose a VBA solution to your question, by creating a Custom formula

Please, 

1-Save your workbook as an Excel Macro-Enabled Workbook (*.xlsm)

2- Or better save the code on a module on your Personal Macro workbook (PERSONAL.XLSB) if you wish to use this function in any workbook.

=GetRowMatchingMymbers(< Numbers To Find range >,< Table with the numbers >)

On the sample picture below

< Numbers To Find range >= A2:E2

< Table with the numbers >=****$I$1:$O$15

If no matches found

Here is the custom function code

**************************************************************************************

Public Function GetRowMatchingMymbers(myNumbers As Range, myTable As Range) As Variant

Dim matchCount As Integer

Dim totalRows As Long

Dim rowRange As Range

Dim num As Range, n As Long

matchCount = Application.WorksheetFunction.Count(myNumbers)

totalRows = myTable.Rows.Count

For n = 1 To totalRows

        Set rowRange = myTable.Offset(n - 1).Resize(1)

                For Each num In myNumbers

                    If IsEmpty(num) Then GoTo skipnum

                        Set found = rowRange.Find(What:=num.Value2, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)

                            If found Is Nothing Then

                                    GoTo skipnum

                            Else

                                    counter = counter + 1

                            End If

skipnum:

                Next num

        If counter <> matchCount Then

                counter = 0

                GoTo nextRowRange

       Else

                GoTo Result

       End If

nextRowRange:

Next n

NAResult:

GetRowMatchingMymbers = "N/A"

Exit Function

Result:

GetRowMatchingMymbers = rowRange.Row

Exit Function

End Function

*****************************************************************************************************

Do let me know if you need more help

On the other hand,

If the answer helped you.

Please, consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance

Regards

Jeovany

Answer 3

A formula solution:

=MATCH(COUNTA(B1:E1),LEN(SUBSTITUTE(INT(MMULT(IFERROR(10^MATCH(I1:O10,B1:E1,0),0),TRANSPOSE(COLUMN(I1:O10)^0))/10),0,"")),0)

Note:

1. The formula may not work in 2007 version but in later versions which support the MMULT function.
2. The formula will work only if there are less than 10 repeats of a B1:E1 value in any single row ie. any  B1:E1 value should not appear over 9 times in any row of the range I1:O10.

Regards,

Amit Tandon

www.excelanytime.com

Answer 4

thank you Amit

i tried your formula it is good and thank you 

and i have also this one 

=AGGREGATE(14,6,A1:A11/(MMULT(COUNTIF(B1:H1,M1:X11),TRANSPOSE(M1:X1)^0)=COUNT(B1:H1)),1)

they booth work correctly 

the issue is the both are too slow with larger tables 

is there any solution for the speed issue?

Answer 5

Jeovany

Thank you 

The code you wrote works and no errors there

however it is very slow 

formulas are slow too but this code is slower significally

is there some way make it faster?

thank you