Matching 2 spreadsheets9tabs) in 1 excel s/s

[Office Girl J]

hi,
Does any one know what formula (ie: Index Match) to match at least 2-3
fields on 2 spreadsheets(data obtained from 2 systems)? If there are any
empty columns in any of the spreadsheet, do we have to remove them in order
for the formula to work?

 

[Dave Peterson]

Without knowing the layout of the data, it's difficult to guess.

Maybe you could use two formulas -- one for each worksheet and match on the
first column and first row.

If the data to match is in each row, then there are formulas for that, too.

If the data being returned is numeric and there are fewer than 2 matches (1
match or no matches), then you may be able to use =sumifs() in xl2007+ or
=sumproduct() in any version.

As for the empty data, you could always check that first:

=if(a1="","", index/match/sumifs/...formula here)

But all those details would depend on how the table is laid out.