If/And statement; VLOOKUP?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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!D1:D4000 for master
 
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!
 
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)
 
Back
Top