|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 12th, 2010, 05:06 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Corresponding Value Based on Comparing Two Sheets of Data
Can someone please help me find a formula (or two) for this example. If you
can show me a couple of ways to do this (so I can learn), I'd greatly appreciate it! Thanks! I'm trying to figure out a formula (or two) that will help me auto-populate the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the "8888" row to return blank, since it does not exist in Sheet 1. Sheet 1: ColumnA ColumnB 1234 20 4321 10 5678 11 8765 - 9999 12 7777 13 Sheet 2: ColumnA ColumnB 5678 ? 8765 ? 1234 ? 4321 ? 8888 ? 
|
|
#2
March 12th, 2010, 05:23 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Corresponding Value Based on Comparing Two Sheets of Data
Sheet 1:
ColumnA ColumnB 8765 - Is that "dash" entered in the cell or does it represent an empty cell? Sheet 2: ColumnA ColumnB 8765 ? So, what result should appear on Sheet2 for 8765? -- Biff Microsoft Excel MVP "PaulQ" wrote in message ... Can someone please help me find a formula (or two) for this example. If you can show me a couple of ways to do this (so I can learn), I'd greatly appreciate it! Thanks! I'm trying to figure out a formula (or two) that will help me auto-populate the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the "8888" row to return blank, since it does not exist in Sheet 1. Sheet 1: ColumnA ColumnB 1234 20 4321 10 5678 11 8765 - 9999 12 7777 13 Sheet 2: ColumnA ColumnB 5678 ? 8765 ? 1234 ? 4321 ? 8888 ?
|
|
#3
March 12th, 2010, 05:36 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Corresponding Value Based on Comparing Two Sheets of Data
This should do what you want:
=if(isna(vlookup(a1,sheet1!A:B,2,false)),"",vlooku p(a1,sheet1!A:B,2,false)) Regards, Fred "PaulQ" wrote in message ... Can someone please help me find a formula (or two) for this example. If you can show me a couple of ways to do this (so I can learn), I'd greatly appreciate it! Thanks! I'm trying to figure out a formula (or two) that will help me auto-populate the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the "8888" row to return blank, since it does not exist in Sheet 1. Sheet 1: ColumnA ColumnB 1234 20 4321 10 5678 11 8765 - 9999 12 7777 13 Sheet 2: ColumnA ColumnB 5678 ? 8765 ? 1234 ? 4321 ? 8888 ?
|
|
#4
March 12th, 2010, 06:28 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Corresponding Value Based on Comparing Two Sheets of Da
The dash is an actual value. That is, the value returned for row 8765 should
be "-". Thanks! "T. Valko" wrote: Sheet 1: ColumnA ColumnB 8765 - Is that "dash" entered in the cell or does it represent an empty cell? Sheet 2: ColumnA ColumnB 8765 ? So, what result should appear on Sheet2 for 8765? -- Biff Microsoft Excel MVP "PaulQ" wrote in message ... Can someone please help me find a formula (or two) for this example. If you can show me a couple of ways to do this (so I can learn), I'd greatly appreciate it! Thanks! I'm trying to figure out a formula (or two) that will help me auto-populate the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the "8888" row to return blank, since it does not exist in Sheet 1. Sheet 1: ColumnA ColumnB 1234 20 4321 10 5678 11 8765 - 9999 12 7777 13 Sheet 2: ColumnA ColumnB 5678 ? 8765 ? 1234 ? 4321 ? 8888 ? .
|
|
#5
March 12th, 2010, 06:38 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Corresponding Value Based on Comparing Two Sheets of Da
That worked perfectly! Thanks, Fred!
Bill, feel free to give your suggestions as well. Again, as a learning opportunity for me (and others) and for a different perspective. Thanks! You guys are great! "Fred Smith" wrote: This should do what you want: =if(isna(vlookup(a1,sheet1!A:B,2,false)),"",vlooku p(a1,sheet1!A:B,2,false)) Regards, Fred "PaulQ" wrote in message ... Can someone please help me find a formula (or two) for this example. If you can show me a couple of ways to do this (so I can learn), I'd greatly appreciate it! Thanks! I'm trying to figure out a formula (or two) that will help me auto-populate the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the "8888" row to return blank, since it does not exist in Sheet 1. Sheet 1: ColumnA ColumnB 1234 20 4321 10 5678 11 8765 - 9999 12 7777 13 Sheet 2: ColumnA ColumnB 5678 ? 8765 ? 1234 ? 4321 ? 8888 ? .
|
|
#6
March 12th, 2010, 04:36 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Corresponding Value Based on Comparing Two Sheets of Da
You're welcome. Thanks for the feedback.
Regards, Fred "PaulQ" wrote in message ... That worked perfectly! Thanks, Fred! Bill, feel free to give your suggestions as well. Again, as a learning opportunity for me (and others) and for a different perspective. Thanks! You guys are great! "Fred Smith" wrote: This should do what you want: =if(isna(vlookup(a1,sheet1!A:B,2,false)),"",vlooku p(a1,sheet1!A:B,2,false)) Regards, Fred "PaulQ" wrote in message ... Can someone please help me find a formula (or two) for this example. If you can show me a couple of ways to do this (so I can learn), I'd greatly appreciate it! Thanks! I'm trying to figure out a formula (or two) that will help me auto-populate the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the "8888" row to return blank, since it does not exist in Sheet 1. Sheet 1: ColumnA ColumnB 1234 20 4321 10 5678 11 8765 - 9999 12 7777 13 Sheet 2: ColumnA ColumnB 5678 ? 8765 ? 1234 ? 4321 ? 8888 ? .
|
|
#7
March 12th, 2010, 05:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Corresponding Value Based on Comparing Two Sheets of Da
show me a couple of ways to do this (so I can learn)
Ok, here'a bunch of examples. This is a good demonstration that shows just how many different ways you can do something. Let's assume your data is setup like this: A2:B7 - 1234 20 4321 10 5678 11 8765 - 9999 12 7777 13 A10:A14 - 5678 8765 1234 4321 8888 Enter any one of these formulas in B10 and copy down to B14: =IF(ISNA(VLOOKUP(A10,A$2:B$7,2,0)),"",VLOOKUP(A10, A$2:B$7,2,0)) =IF(ISERROR(VLOOKUP(A10,A$2:B$7,2,0)),"",VLOOKUP(A 10,A$2:B$7,2,0)) =IF(ISNA(MATCH(A10,A$2:A$7,0)),"",VLOOKUP(A10,A$2: B$7,2,0)) =IF(ISERROR(MATCH(A10,A$2:A$7,0)),"",VLOOKUP(A10,A $2:B$7,2,0)) =IF(ISNUMBER(MATCH(A10,A$2:A$7,0)),VLOOKUP(A10,A$2 :B$7,2,0),"") =IF(COUNT(MATCH(A10,A$2:A$7,0)),VLOOKUP(A10,A$2:B$ 7,2,0),"") =IF(COUNTIF(A$2:B$7,A10),VLOOKUP(A10,A$2:B$7,2,0), "") =IF(ISNA(MATCH(A10,A$2:A$7,0)),"",INDEX(B$2:B$7,MA TCH(A10,A$2:A$7,0))) =IF(ISERROR(MATCH(A10,A$2:A$7,0)),"",INDEX(B$2:B$7 ,MATCH(A10,A$2:A$7,0))) =IF(ISNUMBER(MATCH(A10,A$2:A$7,0)),INDEX(B$2:B$7,M ATCH(A10,A$2:A$7,0)),"") =IF(COUNT(MATCH(A10,A$2:A$7,0)),INDEX(B$2:B$7,MATC H(A10,A$2:A$7,0)),"") =IF(COUNTIF(A$2:A$7,A10),INDEX(B$2:B$7,MATCH(A10,A $2:A$7,0)),"") =IF(COUNTIF(A$2:A$7,A10),OFFSET(B$2,MATCH(A10,A$2: A$7,0)-1,0),"") =IF(ISNA(MATCH(A10,A$2:A$7,0)),"",OFFSET(B$2,MATCH (A10,A$2:A$7,0)-1,0)) =IF(ISERROR(MATCH(A10,A$2:A$7,0)),"",OFFSET(B$2,MA TCH(A10,A$2:A$7,0)-1,0)) =IF(ISNUMBER(MATCH(A10,A$2:A$7,0)),OFFSET(B$2,MATC H(A10,A$2:A$7,0)-1,0),"") =IF(COUNT(MATCH(A10,A$2:A$7,0)),OFFSET(B$2,MATCH(A 10,A$2:A$7,0)-1,0),"") I might have missed a few! -- Biff Microsoft Excel MVP "PaulQ" wrote in message ... The dash is an actual value. That is, the value returned for row 8765 should be "-". Thanks! "T. Valko" wrote: Sheet 1: ColumnA ColumnB 8765 - Is that "dash" entered in the cell or does it represent an empty cell? Sheet 2: ColumnA ColumnB 8765 ? So, what result should appear on Sheet2 for 8765? -- Biff Microsoft Excel MVP "PaulQ" wrote in message ... Can someone please help me find a formula (or two) for this example. If you can show me a couple of ways to do this (so I can learn), I'd greatly appreciate it! Thanks! I'm trying to figure out a formula (or two) that will help me auto-populate the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the "8888" row to return blank, since it does not exist in Sheet 1. Sheet 1: ColumnA ColumnB 1234 20 4321 10 5678 11 8765 - 9999 12 7777 13 Sheet 2: ColumnA ColumnB 5678 ? 8765 ? 1234 ? 4321 ? 8888 ? .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
