Comparing two different spreadsheets

  • Thread starter Thread starter Hyun
  • Start date Start date
H

Hyun

I need to compare two different spreadsheets. One spreadsheet has ticket
numbers and amounts. The other spreadsheet has ticket number, amount, name,
and product name. I need to be able to compare ticket number and amount on
both spreadsheet. If they match, the name needs to be entered on the
spreadsheet with only the ticket number and amount. I also need the product
name to be move but I can repeat the previous solution unless there is a way
to do both at the same time.
 
One way to achieve the lookup based on 2 col uniques
is to use an array -entered index/match of this structure:
index(ycolC,match(1,(yColA=A2)*(yColB=B2),0))

Illustrated in this working sample:
http://www.freefilehosting.net/download/3ge6g
Lookup based on 2 col uniques across 2 shts.xls

In x,
In C2, array-entered (ie press CTRL+SHIFT+ENTER to confirm the formula):
=IF(COUNTA(A2:B2)<2,"",INDEX(y!C$2:C$10,MATCH(1,(y!$A$2:$A$10=$A2)*(y!$B$2:$B$10=$B2),0)))
Copy C2 to D2, fill down. Adapt the ranges to suit.

where
x = sheet with ticket numbers and amounts
y= other sheet with ticket number, amount, name, and product name
 
Back
Top