I am trying to get a result by matching 2 spreadsheets ,so sheet2 match trid and btn to sheet1 and give result match.
sheet1
A B
trid btn
1 75
2 22
2 540
Sheet2
A B C
trid btn
1 20
2 86
2 45
2 540 Match
I am trying to get a result by matching 2 spreadsheets ,so sheet2 match trid and btn to sheet1 and give result match.
sheet1
A B
trid btn
1 75
2 22
2 540
Sheet2
A B C
trid btn
1 20
2 86
2 45
2 540 Match
Last edited by semo; 05-28-2009 at 06:05 AM.
Put this array formula in C2 and copy it down:
=IF(ISNUMBER(MATCH(A2&"-"&B2,Sheet1!$A$2:$A$20&"-"&Sheet1!$B$2:$B$20,0)),"Match","")
...that is an array formula, so confirm it with CTRL-SHIFT-ENTER to activate the array. If you just press ENTER it will not work, press F2 on the cell and then CTRL-SHIFT-ENTER to correct.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Non Array
C2: =REPT("Match",ISNUMBER(MATCH(1,INDEX((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1:$B$100=$B2),0),0)))
copy down as required
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks