Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and extract
Hi,
I have one worksheet, two different tab. There are two customer number columns in Sheet1. One of the two would have the customer number and the other one would be blank. Column C has an amount. Col A Col B Col C Col D (Formula to bring the amount from sheet2) 245600 50000 138956 25000 965241 14589 Sheet2 has one customer number column and an amount column. Col A Col B 245600 50000 138956 25000 965241 14589 In Sheet1, I like to bring in the amount of Sheet2 based on matching of the customer number of Sheet1 which is in two different columns. Thanks, Dinesh |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and extract
Dinesh wrote:
Hi, I have one worksheet, two different tab. There are two customer number columns in Sheet1. One of the two would have the customer number and the other one would be blank. Column C has an amount. Col A Col B Col C Col D (Formula to bring the amount from sheet2) 245600 50000 138956 25000 965241 14589 Sheet2 has one customer number column and an amount column. Col A Col B 245600 50000 138956 25000 965241 14589 In Sheet1, I like to bring in the amount of Sheet2 based on matching of the customer number of Sheet1 which is in two different columns. Thanks, Dinesh Try this: =INDEX(Sheet2!$B$1:$B$3,IF(Sheet1!A1="",MATCH(Shee t1!B1,Sheet2!$A$1:$A$3,0),MATCH(Sheet1!A1,Sheet2!$ A$1:$A$3,0))) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and extract
One way using index/match, with minimal error trapping
Data assumed running in row2 down In Sheet1, In C2: =IF(A2="",INDEX(Sheet2!B:B,MATCH(B2,Sheet2!A:A,0)) ,INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))) Copy down -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Dinesh" wrote: I have one worksheet, two different tab. There are two customer number columns in Sheet1. One of the two would have the customer number and the other one would be blank. Column C has an amount. Col A Col B Col C Col D (Formula to bring the amount from sheet2) 245600 50000 138956 25000 965241 14589 Sheet2 has one customer number column and an amount column. Col A Col B 245600 50000 138956 25000 965241 14589 In Sheet1, I like to bring in the amount of Sheet2 based on matching of the customer number of Sheet1 which is in two different columns. Thanks, Dinesh |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and extract
Try this:
=VLOOKUP(MAX(A1:B1),Sheet2!A:B,2,) copy down "Dinesh" wrote: Hi, I have one worksheet, two different tab. There are two customer number columns in Sheet1. One of the two would have the customer number and the other one would be blank. Column C has an amount. Col A Col B Col C Col D (Formula to bring the amount from sheet2) 245600 50000 138956 25000 965241 14589 Sheet2 has one customer number column and an amount column. Col A Col B 245600 50000 138956 25000 965241 14589 In Sheet1, I like to bring in the amount of Sheet2 based on matching of the customer number of Sheet1 which is in two different columns. Thanks, Dinesh |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and extract
Try this...
One of the two would have the customer number and the other one would be blank Assuming that your customer numbers are in fact numeric numbers and not alpha-numeric text strings. Assume on Sheet1 your customer *numbers* can be in either column A or column B (but will never be in both at the same time). Sheet2: Column A = customer numbers Column B = amounts Enter this formula on Sheet1 in cell C1: =SUMIF(Sheet2!A:A,SUM(A1:B1),Sheet2!B:B) Or: =SUMIF(Sheet2!A:A,A1+B1,Sheet2!B:B) Copy down as needed. -- Biff Microsoft Excel MVP "Dinesh" wrote in message ... Hi, I have one worksheet, two different tab. There are two customer number columns in Sheet1. One of the two would have the customer number and the other one would be blank. Column C has an amount. Col A Col B Col C Col D (Formula to bring the amount from sheet2) 245600 50000 138956 25000 965241 14589 Sheet2 has one customer number column and an amount column. Col A Col B 245600 50000 138956 25000 965241 14589 In Sheet1, I like to bring in the amount of Sheet2 based on matching of the customer number of Sheet1 which is in two different columns. Thanks, Dinesh |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match and extract
"T. Valko" wrote: Try this... One of the two would have the customer number and the other one would be blank Assuming that your customer numbers are in fact numeric numbers and not alpha-numeric text strings. Assume on Sheet1 your customer *numbers* can be in either column A or column B (but will never be in both at the same time). Sheet2: Column A = customer numbers Column B = amounts Enter this formula on Sheet1 in cell C1: =SUMIF(Sheet2!A:A,SUM(A1:B1),Sheet2!B:B) Or: =SUMIF(Sheet2!A:A,A1+B1,Sheet2!B:B) Copy down as needed. -- Biff Microsoft Excel MVP "Dinesh" wrote in message ... Hi, I have one worksheet, two different tab. There are two customer number columns in Sheet1. One of the two would have the customer number and the other one would be blank. Column C has an amount. Col A Col B Col C Col D (Formula to bring the amount from sheet2) 245600 50000 138956 25000 965241 14589 Sheet2 has one customer number column and an amount column. Col A Col B 245600 50000 138956 25000 965241 14589 In Sheet1, I like to bring in the amount of Sheet2 based on matching of the customer number of Sheet1 which is in two different columns. Thanks, Dinesh HI, I made a mistake in which sheet do i need an answer? Actually I like to match the Sheet2's customer number and bring the amount of sheet1 into sheeet2 which is the opposit of what I stated earlier. Thanks, Dinesh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP or MATCH and/or INDEX to extract value on a given date | Excel Worksheet Functions | |||
Extract or report multiple arrays if a cell match a criteria | Excel Discussion (Misc queries) | |||
Help me, compare 2 sheet and extract the match data into the new sheet. | Excel Discussion (Misc queries) | |||
Compare and match names and extract a cell content | Excel Worksheet Functions | |||
formula to extract specific data if match occurs | Excel Worksheet Functions |