Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup question
Hi All,
I asked this question previously but perhaps did not explain it too well, I will try again. I have two different spreadsheets, A & B in excel 2007 and am trying to compare like Customer ID Numbers between the two spreadsheets. Spreadsheet A is a weekly report of sales and has the following columns: Cust ID, Cust Name, Sales Order #, Date, Status, Sales Amount & Sales Rep. the length always changes from week to week, but the columns remain the same. Spreadsheet B is a running report of customers that our telemarketer has brought into our store. It has many columns, but one of which is the CustID, just like in the previous Spreadsheet A. I would like to run a formula, in Spreadsheet A that will take the Cust ID on each row in Spreadsheet A and compare it to the list of those Cust ID's in Spreadsheet B. If the Cust ID is not found, do nothing. If the Cust ID is found - I would then like to have the Sales Amount value brought in as the result. Column ID's in Spreadsheet A: 1st Column (A) is Cust ID 6th Column (F) is Sales Amount 9th Column (I) is where I would like this formula (and result if the Cust ID is on the other spreadsheet) to go. Column ID in Spreadsheet B: Cust ID is in column H and will either have a four digit numerical number representing the Customer ID Number, or it will be blank. I can reformat the number to be text if that will help in the lookup. Thanks for your help, if you have any questions, or if I can clarify the example better, please let me know. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup question
Hi,
Not sure if this is what you're looking for but maybe: =IF(COUNTIF(Sheet2!$H$1:$H$1000,A1)0,F1,"") adjust to suit and copy down as far as needed. HTH Jean-Guy "MarkT" wrote: Hi All, I asked this question previously but perhaps did not explain it too well, I will try again. I have two different spreadsheets, A & B in excel 2007 and am trying to compare like Customer ID Numbers between the two spreadsheets. Spreadsheet A is a weekly report of sales and has the following columns: Cust ID, Cust Name, Sales Order #, Date, Status, Sales Amount & Sales Rep. the length always changes from week to week, but the columns remain the same. Spreadsheet B is a running report of customers that our telemarketer has brought into our store. It has many columns, but one of which is the CustID, just like in the previous Spreadsheet A. I would like to run a formula, in Spreadsheet A that will take the Cust ID on each row in Spreadsheet A and compare it to the list of those Cust ID's in Spreadsheet B. If the Cust ID is not found, do nothing. If the Cust ID is found - I would then like to have the Sales Amount value brought in as the result. Column ID's in Spreadsheet A: 1st Column (A) is Cust ID 6th Column (F) is Sales Amount 9th Column (I) is where I would like this formula (and result if the Cust ID is on the other spreadsheet) to go. Column ID in Spreadsheet B: Cust ID is in column H and will either have a four digit numerical number representing the Customer ID Number, or it will be blank. I can reformat the number to be text if that will help in the lookup. Thanks for your help, if you have any questions, or if I can clarify the example better, please let me know. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup question
Hi Mark
If I have understood you correctly then =IFERROR(VLOOKUP($A2,SheetB!$H:$H,1,0)),"",F2) -- Regards Roger Govier "MarkT" wrote in message ... Hi All, I asked this question previously but perhaps did not explain it too well, I will try again. I have two different spreadsheets, A & B in excel 2007 and am trying to compare like Customer ID Numbers between the two spreadsheets. Spreadsheet A is a weekly report of sales and has the following columns: Cust ID, Cust Name, Sales Order #, Date, Status, Sales Amount & Sales Rep. the length always changes from week to week, but the columns remain the same. Spreadsheet B is a running report of customers that our telemarketer has brought into our store. It has many columns, but one of which is the CustID, just like in the previous Spreadsheet A. I would like to run a formula, in Spreadsheet A that will take the Cust ID on each row in Spreadsheet A and compare it to the list of those Cust ID's in Spreadsheet B. If the Cust ID is not found, do nothing. If the Cust ID is found - I would then like to have the Sales Amount value brought in as the result. Column ID's in Spreadsheet A: 1st Column (A) is Cust ID 6th Column (F) is Sales Amount 9th Column (I) is where I would like this formula (and result if the Cust ID is on the other spreadsheet) to go. Column ID in Spreadsheet B: Cust ID is in column H and will either have a four digit numerical number representing the Customer ID Number, or it will be blank. I can reformat the number to be text if that will help in the lookup. Thanks for your help, if you have any questions, or if I can clarify the example better, please let me know. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup question
=IF(ISNA(VLOOKUP([Book2]Sheet1!H2,$A$2:$F$6,6,FALSE))=TRUE,"
",VLOOKUP([Book2]Sheet1!H2,$A$2:$F$6,6,FALSE)) This formula seemed to work when I did a little test. Here is what it does: It looks in Book2, Sheet 1(Your spreadsheet B) at H2 which is the Cust ID. It then looks at the range of all of your data in spreadsheet A and if it finds an exact match it takes the data from the 6th column over (which should be the sales amount) and displays it. (The FALSE part tells it to look for an exact ID match.) The formula above is essentially liste twice becasue the 1st part is looking to see if the result is N/A, if it is it will display blank (This means if could not find a cust id), if it does find a valid cust id it will show the result. Does this make sense? "MarkT" wrote: Hi All, I asked this question previously but perhaps did not explain it too well, I will try again. I have two different spreadsheets, A & B in excel 2007 and am trying to compare like Customer ID Numbers between the two spreadsheets. Spreadsheet A is a weekly report of sales and has the following columns: Cust ID, Cust Name, Sales Order #, Date, Status, Sales Amount & Sales Rep. the length always changes from week to week, but the columns remain the same. Spreadsheet B is a running report of customers that our telemarketer has brought into our store. It has many columns, but one of which is the CustID, just like in the previous Spreadsheet A. I would like to run a formula, in Spreadsheet A that will take the Cust ID on each row in Spreadsheet A and compare it to the list of those Cust ID's in Spreadsheet B. If the Cust ID is not found, do nothing. If the Cust ID is found - I would then like to have the Sales Amount value brought in as the result. Column ID's in Spreadsheet A: 1st Column (A) is Cust ID 6th Column (F) is Sales Amount 9th Column (I) is where I would like this formula (and result if the Cust ID is on the other spreadsheet) to go. Column ID in Spreadsheet B: Cust ID is in column H and will either have a four digit numerical number representing the Customer ID Number, or it will be blank. I can reformat the number to be text if that will help in the lookup. Thanks for your help, if you have any questions, or if I can clarify the example better, please let me know. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup question | Excel Worksheet Functions | |||
VLOOKUP Question | Excel Worksheet Functions | |||
vlookup vba question. | Excel Discussion (Misc queries) | |||
If/And/Vlookup question | Excel Discussion (Misc queries) | |||
Question on Vlookup | Excel Worksheet Functions |