Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
match vlookup function
Hello
A report is send to me has 27 columns and at least 900 lines I need 3 columns column a name of customer column c has type of transaction column d has number of transactions Example Macys Fax 10 Target PH 15 Macys ph 25 Macys Tot 35 Target Fax 5 Target Tot 20 K mart Fax 20 K mart PH 15 K mart Tot 35 My report is column C I have name of customer Column E I have Total Column F I have Fax Column G I have PH Using the example above my report should look like this the example above should be Customer Total fax PH Macys 35 10 25 I tried vlookup and MATCH and does not work Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
match vlookup function
Assuming the data from the report you get is in A1:D10 with headers in Row1.
In G1:J1 you have the below headers. Note that the header text is exactly similar to the one in the data example 'Tot' and not 'total' Cust Tot Fax PH Macys xx xx xx Use the below formula in G2 and copy that to H2 and J2 ...and once done copy the formula down for all 3 cells.. =SUMPRODUCT(--($A$2:$A$10=$G2),--($C$2:$C$10=H$1),--($D$2:$D$10)) If this post helps click Yes --------------- Jacob Skaria "Wanna Learn" wrote: Hello A report is send to me has 27 columns and at least 900 lines I need 3 columns column a name of customer column c has type of transaction column d has number of transactions Example Macys Fax 10 Target PH 15 Macys ph 25 Macys Tot 35 Target Fax 5 Target Tot 20 K mart Fax 20 K mart PH 15 K mart Tot 35 My report is column C I have name of customer Column E I have Total Column F I have Fax Column G I have PH Using the example above my report should look like this the example above should be Customer Total fax PH Macys 35 10 25 I tried vlookup and MATCH and does not work Thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
match vlookup function
G2 is name and the formula cells are H2, I2 and J2
If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Assuming the data from the report you get is in A1:D10 with headers in Row1. In G1:J1 you have the below headers. Note that the header text is exactly similar to the one in the data example 'Tot' and not 'total' Cust Tot Fax PH Macys xx xx xx Use the below formula in G2 and copy that to H2 and J2 ...and once done copy the formula down for all 3 cells.. =SUMPRODUCT(--($A$2:$A$10=$G2),--($C$2:$C$10=H$1),--($D$2:$D$10)) If this post helps click Yes --------------- Jacob Skaria "Wanna Learn" wrote: Hello A report is send to me has 27 columns and at least 900 lines I need 3 columns column a name of customer column c has type of transaction column d has number of transactions Example Macys Fax 10 Target PH 15 Macys ph 25 Macys Tot 35 Target Fax 5 Target Tot 20 K mart Fax 20 K mart PH 15 K mart Tot 35 My report is column C I have name of customer Column E I have Total Column F I have Fax Column G I have PH Using the example above my report should look like this the example above should be Customer Total fax PH Macys 35 10 25 I tried vlookup and MATCH and does not work Thanks in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
match vlookup function
if your data have similar column headings you can try HLOOKUP.if you need i can explain you how to use hlookup.but i think it is not hard for you if you already know vlookup. "Wanna Learn" wrote: Hello A report is send to me has 27 columns and at least 900 lines I need 3 columns column a name of customer column c has type of transaction column d has number of transactions Example Macys Fax 10 Target PH 15 Macys ph 25 Macys Tot 35 Target Fax 5 Target Tot 20 K mart Fax 20 K mart PH 15 K mart Tot 35 My report is column C I have name of customer Column E I have Total Column F I have Fax Column G I have PH Using the example above my report should look like this the example above should be Customer Total fax PH Macys 35 10 25 I tried vlookup and MATCH and does not work Thanks in advance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
match vlookup function
Saved from a previous post:
If you want exact matches for just two columns (and return a value from a third), you could use: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100),0)) (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) ============ If there is only one match and you're bringing back a number (or 0 if there is no match for all the criteria), you can use: =sumproduct(--(othersheet!a1:a10=a1), --(othersheet!b1:b10=b1), (othersheet!c1:c10)) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Wanna Learn wrote: Hello A report is send to me has 27 columns and at least 900 lines I need 3 columns column a name of customer column c has type of transaction column d has number of transactions Example Macys Fax 10 Target PH 15 Macys ph 25 Macys Tot 35 Target Fax 5 Target Tot 20 K mart Fax 20 K mart PH 15 K mart Tot 35 My report is column C I have name of customer Column E I have Total Column F I have Fax Column G I have PH Using the example above my report should look like this the example above should be Customer Total fax PH Macys 35 10 25 I tried vlookup and MATCH and does not work Thanks in advance -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VlookUp or may be Match Function | Excel Worksheet Functions | |||
Vlookup or match function? | Excel Worksheet Functions | |||
Vlookup/Match Function | Excel Worksheet Functions | |||
vlookup/ match or other function?? | Excel Worksheet Functions | |||
MATCH function in a VLOOKUP | Excel Worksheet Functions |