S
Stumped
For example, how do I match product codes and warehouses to return an unique
price?
price?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
JLGWhiz
It is a lot easier to answer a question like that if the rows and columnns
data is provided. After all, nobody on this side of the server can see
your worksheet.
data is provided. After all, nobody on this side of the server can see
your worksheet.
R
Ryan H
You are way to vague with your post. We need more details of what you are
trying to accomplish.
trying to accomplish.
J
Jacob Skaria
With data arranged as below and the query product code and Ware house in
cells D1 and D2 respectively try the below array formula..
Col A Col B Col C
P.Code WHouse Price
10001 A 12.5
10001 B 13.5
10002 A 15
10002 B 16
10003 A 8
10003 B 9
An array formula can perform multiple calculations and then return either a
single result or multiple results. You create array formulas in the same way
that you create other formulas, except you press CTRL+SHIFT+ENTER to enter
the formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula>}"
=INDEX($C$2:$C$9,MATCH(1,($A$2:$A$9=D2)*($B$2:$B$9=D3),0))
If you are looking for a VBA solution try the below
Sub Macro()
Dim varPCode As Variant
Dim varWHouse As Variant
varPCode = 10001
varWHouse = "B"
MsgBox Evaluate("INDEX($C$3:$C$10,MATCH(1," & _
"($A$3:$A$10=" & varPCode & ")*($B$3:$B$10=""" & varWHouse & """),0))")
End Sub
cells D1 and D2 respectively try the below array formula..
Col A Col B Col C
P.Code WHouse Price
10001 A 12.5
10001 B 13.5
10002 A 15
10002 B 16
10003 A 8
10003 B 9
An array formula can perform multiple calculations and then return either a
single result or multiple results. You create array formulas in the same way
that you create other formulas, except you press CTRL+SHIFT+ENTER to enter
the formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula>}"
=INDEX($C$2:$C$9,MATCH(1,($A$2:$A$9=D2)*($B$2:$B$9=D3),0))
If you are looking for a VBA solution try the below
Sub Macro()
Dim varPCode As Variant
Dim varWHouse As Variant
varPCode = 10001
varWHouse = "B"
MsgBox Evaluate("INDEX($C$3:$C$10,MATCH(1," & _
"($A$3:$A$10=" & varPCode & ")*($B$3:$B$10=""" & varWHouse & """),0))")
End Sub