match vlookup function

[Wanna Learn]

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

 

[Jacob Skaria]

Assuming the data from the report you get is in A110 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]

Assuming the data from the report you get is in A110 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]

G2 is name and the formula cells are H2, I2 and J2

If this post helps click Yes

 

[Jacob Skaria]

G2 is name and the formula cells are H2, I2 and J2

If this post helps click Yes

 

[Satyendra_Haldaur]

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.

 

[Satyendra_Haldaur]

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.

 

[Dave Peterson]

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Dave Peterson]

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html