How do I match 2 items in excel to return a unique value?

  • Thread starter Thread starter Stumped
  • Start date Start date
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.
 
You are way to vague with your post. We need more details of what you are
trying to accomplish.
 
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
 
Back
Top