CODEWITHSUNDEEP
excelvbaarray-formulas
surprisedbadger
surprisedbadger

Reputation: 74

find matching rows excel formula or vba

I have an excel file with two sheets and identical formats on each. I need to search vertically down the sheet and find the matching row on the other sheet on the and return the value of a cell from the that row, but only if the entire row matches.

I tried could not find an exact answer for this. Any help is appreciated. thanks!

Upvotes: 0

Views: 279

Answers (3)

EEM
EEM

Reputation: 6659

Assuming worksheets are named Sheet1 and Sheet2 (change as required), their data structure is identical and data ranges are A1:I100 in each (change as required).

In a third sheet enter this FormulaArray in A1 and copy till last record (A100)

Enter the FormulaArray pressing [Ctrl] + [Shift] + [Enter] simultaneously, you shall see {...} around the formula if entered correctly

=SUM( ( Sheet1!$A1:$I1 = Sheet2!$A$1:$I$100 ) * 1 ) = COLUMNS( $A1:$I1 )

Then in B1 enter this formula copy till J1 then till last record (B2:J100)

=IF( $A1 , Sheet1!A1 ,"" )

You can also enter this formula in Sheet1!J1 and copy till last record:

=SUM( ( $A1:$I1 = Sheet2!$A$1:$I$100 ) * 1 ) = COLUMNS( $A1:$I1 )

Or enter this formula in Sheet2!J1 and copy till last record:

=SUM( ( $A1:$I1 = Sheet1!$A$1:$I$100 ) * 1 ) = COLUMNS( $A1:$I1 )

Upvotes: 0

pnuts
pnuts

Reputation: 59475

A simple way is, in a third sheet, populate the range A1:I~100 with:

 =IF(Sheet1!A1=Sheet2!A1,Sheet1!A1,"")

and J1 down to suit with: 

=COUNTBLANK(A1:I1)

Filter ColumnJ to select 0 and pick you required values from those rows.

Upvotes: 0

teylyn
teylyn

Reputation: 35915

Not pretty, but it works for a limited data set (in this case rows 1 to 10):

=INDEX(B!$I$1:$I$10,MATCH(A!A1&A!B1&A!C1&A!D1&A!E1&A!F1&A!G1&A!H1&A!I1,INDEX(B!$A$1:$A$10&B!$B$1:$B$10&B!$C$1:$C$10&B!$D$1:$D$10&B!$E$1:$E$10&B!$F$1:$F$10&B!$G$1:$G$10&B!$H$1:$H$10&B!$I$1:$I$10,0),0))

Data structure in sheets A and B is identical. The formula returns the value from Sheet B, column I from the rows where the values in columns A to I are identical in both sheets.

It should be fine with 100 rows, but don't do this with whole column references.

Upvotes: 1

Related Questions