|
|
Hello!
I tried searching in the archives for help but could not find similar problem.
And I do not know Excel programming, only formulas.
Right now I have 3 columns of data.
Column A = Order #
Column B = Order's Line #
Column C = Amout of order
Data example:
A B C
5234 10 $35
5234 20 $55
5234 30 $40
5236 10 $89
5236 20 $70
What I need to do is do a vlookup that will pick up the value in column C if values in column A and B are same.
For example, for Order # 5234 and Line # 30, the return value should be $40.
After reading the message archives, I tried Index function also but could not get it to work.
Any suggestions would be appreciated.
Thank you!
Would these combinations of A amp; B always be unique? If so, you could try this if column A and B are truly numbers:
=sumproduct(--(A1:A10000=5234),--(B1:B10000=30),C1:C10000)
If they are text then try this:
=sumproduct(--(A1:A10000="5234"),--(B1:B10000="30"),C1:C10000)
Hope that hekps and the values after the = sign can also be cell references.
What if column C were text...
Constrol+shift+enter, not just enter...
=INDEX($C$2 C$6,MATCH(1,IF($A$2A$6=E2,IF($B$2:B$6=F2,1)),0))
where E2 houses an order # of interest and F2 an order line of interest.
Originally Posted by tsunami1977Hello!
I tried searching in the archives for help but could not find similar problem.
And I do not know Excel programming, only formulas.
Right now I have 3 columns of data.
Column A = Order #
Column B = Order's Line #
Column C = Amout of order
Data example:
A B C
5234 10 $35
5234 20 $55
5234 30 $40
5236 10 $89
5236 20 $70
What I need to do is do a vlookup that will pick up the value in column C if values in column A and B are same.
For example, for Order # 5234 and Line # 30, the return value should be $40.
After reading the message archives, I tried Index function also but could not get it to work.
Any suggestions would be appreciated.
Thank you!
Nice
Thank you Schielrn and Aladin for your help.
Both formulas worked very well.
I ran into one problem.
For column B, instead of Order Line #, the sales team gave me Order Description which is in text.
I could get the formulas to work if the Order Line # is there, but the formula does not work with text. Not sure if anyone has any suggestions.
Thank you for the help! I love this forum!
Originally Posted by tsunami1977Thank you Schielrn and Aladin for your help.
Both formulas worked very well.
I ran into one problem.
For column B, instead of Order Line #, the sales team gave me Order Description which is in text.
I could get the formulas to work if the Order Line # is there, but the formula does not work with text. Not sure if anyone has any suggestions.
Thank you for the help! I love this forum!
What did you try?
I tried both the Index formula and the sumproduct formula.
I thought the Index formula would work, but it gave me #Value when I set it.
It turns out that the "sumproduct" formula from Schielrn works with the text string.
The problem was that the sales team gave me data that need to be converted (text to column; I do not know what it correct term for this) in order for the comparison to work.
It works perfectly now.
Thanks a lot to everyone!
Originally Posted by tsunami1977I tried both the Index formula and the sumproduct formula.
I thought the Index formula would work, but it gave me #Value when I set it.
SumProduct is a not a look up function.
The Index formula requires applying control+shift+enter, not just enter. |
|
Post at 2011-5-24 18:44
| This Author's Posts Only
