|
If/And statement; VLOOKUP?
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 "o1darcie1o" wrote: 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 |
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 "o1darcie1o" wrote in message ... 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 |
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! "N Harkawat" wrote: 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 "o1darcie1o" wrote in message ... 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 |
Peo:
Every item on sheet 1 exists on sheet 2. Darcie "Peo Sjoblom" wrote: 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 "o1darcie1o" wrote: 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 |
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) "o1darcie1o" wrote in message ... 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! "N Harkawat" wrote: 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 "o1darcie1o" wrote in message ... 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 |
| All times are GMT +1. The time now is 04:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com