• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

FIND EXACT MATCH AND RETURN ROW NUMBER WHERE FOUND

MIKA1122

New Member
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
 

Attachments

bosco_yip

Excel Ninja
Return the row number in 4 numbers range (B1:E1) matching with table J1:O1

In G1, enter formula :

=AGGREGATE(14,6,A1:A10/(MMULT(COUNTIF(B1:E1,I1:O10),{1;1;1;1;1;1;1})=4),1)

or,

=SUMPRODUCT(A1:A10*(MMULT(COUNTIF(B1:E1,I1:O10),{1;1;1;1;1;1;1})=4))

69885
 
Last edited:

MIKA1122

New Member
Thank you so much
aggregate looks working smooth and faster
one more question lets say I need to change size of table and of the row like in attached file
what parameters i have to change in your formula to make it work correctly
 

Attachments

bosco_yip

Excel Ninja
Adopt the same logic try this,

In J1, enter array formula (Ctrl+Shift+Enter) :

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

or,

using {1;1;1;1;1;1;1;1;1;1;1;1} instead of TRANSPOSE(M1:X1)^0

then the formula become non-array entry, just normal formula >>

=AGGREGATE(14,6,A1:A11/(MMULT(COUNTIF(B1:H1,M1:X11),{1;1;1;1;1;1;1;1;1;1;1;1})=COUNT(B1:H1)),1)


69894
 

Peter Bartholomew

Well-Known Member
Not the way I normally take a discussion but here I have used a helper range to assist the calculation!

69935

The helper range is a single column table, 'assessment', to the right of the data, and containing the number 1, formatted as a .
The formula for 'success'
= SIGN( AND( COUNTIFS(data[@], criteria) ) )
may either be placed in the column or, as here, in a named formula.
The formula for the row number is simply
= MATCH(1, assessment, 0)
The above is a list solution rather than a 'proper' array solution but I think it offers a valid alternative.

Note: The criteria, data and assessment ranges are each defined by a table to simplify name management and the task of referencing a data row.
 

Attachments

MIKA1122

New Member
the solution by bosco_yip works smooth and fast and i am testing it actually right now
thank you so much bosco_yip


and thank you too Peter
does your SIGN solution make the thing faster or more precise?
i cant test it cause it is not in readytose mode and i am not in state to fill in missing information
 

Peter Bartholomew

Well-Known Member
The SIGN function itself does very little; it simply turns {TRUE,FALSE} into {1,0} so that I can play tricks with fancy number formatting.
Setting up the tables is a moments work if you know what you are doing; the catch is that named Tables without header rows are not a mainstay of the traditional Excel 'tips and tricks' curriculum.
The only advantage is that if, by design or accident, one of the criteria is present multiple times in a data row, the single row formula will not double count it (I have tested each value in the criterion range to see whether it occurs in a data row rather than the reverse process of testing each data value to see whether it can be found within the criterion range).
 

MIKA1122

New Member
thank you Peter
since you and others paid attention to this little task may be you have some way to speed it up ?
this AGGREGATE is working very well and smooth and at short ranges with good speed too
however at large run it kinda slows down not a big deal but if there is a way to make it faster would be appreciated .
 

Peter Bartholomew

Well-Known Member
At first sight, I would not expect a big difference in efficiency between the two approaches. It is possible to increase the number of data rows in the List approach without recalculating the prior records but that is a very specific use case. If you are adding values to the criteria range or columns to the data then a complete recalculation will be needed in either case.

If continual changes to the data set are likely then the formulas I have proposed adjust automatically as the Table resize handles move to accommodate additional data.
 

MIKA1122

New Member
yes may be there is no way for significant change in speed
thank you for taking your time and effort to help me
 

MIKA1122

New Member
hi again
is it possible to stop it when first match found and return first match row number ?
because it is looking until the last match found and it makes it slower
thank you
 

Peter Bartholomew

Well-Known Member
I suspect the savings will be modest unless there are multiple matches and so the first is likely to be early in the sequence.
What I have included is a relative reference to the assessment immediately preceding the current test.

= IF( found, -1, SIGN( AND( COUNTIFS(data[@], criteria) ) ) )

If that is other than zero the current row will not be evaluated.
 

Attachments

vletm

Excel Ninja
MIKA1122
Forums Rule are for You too.
Seems that You have skipped those elsewhere too.
 
Top