If/And statement; VLOOKUP?

[Guest]

I want Excel to look at column a, then at column b, and based on the
combination compared to sheet2, return the value from sheet2, col C.
As detailed as I can:
Sheet1 (<--this is the actual name of the sheet - easier for getting help!!)
A(Cust) B(Svc) C(Acct) D(Master) E(Ledger)
1006 DJNS
1006 ER
1214 DJNS
1345 PIR
Sheet2 - Exact same column mapping, but C, D, & E contain data based on the
combination of A & B. 1006 appears in both DJNS & ER, and they will have
different account numbers (column C) depending on which it is. The sheets do
not have the same number of lines, so I need excel to go through both columns
and get an exact match.
I really hope that makes sense!!

I tried: =IF(AND($A1=SHEET2!$A1,$B1=SHEET2!$B1),SHEET2!C1,"") and copied
down my list over 2000 lines, but only some of the information worked.

Thanks in advance!! Darcie

 

[Guest]

If that does not work, the values are not the same, could be a hidden space
etc.
Just test each cell by itself,

$A1=SHEET2!$A1

$B1=SHEET2!$B1

they should return TRUE if the are the same.




Regards,

Peo Sjoblom

 

[N Harkawat]

On sheet 1 on column C(acct) enter
=INDEX(Sheet2!C1:C4000,MATCH($A$1&$B$1,Sheet2!$A$1:$A$4000&Sheet2!$B$1:$B$4000,0))
array entered (ctrl+shift+enter)

where column C on sheet 2 contains the account #
copy this formula down across all your lines
Same way just change the column reference for master and ledger
in the example above instead of Sheet2!C1:C4000 for Account # use
Sheet2!D14000 for master

 

[Guest]

Hi N:
Formula used:
=INDEX(Sheet2!C2:C2960,MATCH($A$2&$B$2,Sheet2!$A$1:$A$2960&Sheet2!$B$2:$B$2:$B$2960,0))
After ctr+shift+enter, added { } around formula. The formula returns a
value of #N/A. Any idea why?
Thanks!

 

[Guest]

Peo:
Every item on sheet 1 exists on sheet 2.
Darcie

 

[N Harkawat]

The size of the arrays have to be same
Hope this amended formula works
=INDEX(Sheet2!C2:C2960,MATCH($A$2&$B$2,Sheet2!$A$2:$A$2960&Sheet2!$B$2:$B$2960,0))
Again array enter it (ctrl+shift+enter)