Return value in 3rd column based on 2 other columns

[Joe M.]

I have a worksheet with 3 columns. I would like to return the value of column
C to another worksheet based upon the values in Col A & B.
A B C
21057 1 63.50
21057 2 26.40
21057 3 44.80
21463 1 12.50
21463 2 38.50
21463 3 55.80

If A = 21463 and B = 1 then return 12.50. Maybe use vlookup? I know this is
probably easy, just can't think of the solution.

Thanks,
Joe M.

 

[dhstein]

Here's one solution - but it requires some intermediate fields

Make a column "D" that is =A1*100+B1
This will give you entries like 2105701, 2105702,2105703, 2146301, etc.
Column E should be =C1
Now you have a table in columns "D" and "E" and you can do a lookup of
2146301, for example, which will return the value you want. It isn't
elegant, but it gets the job done. I hope that helps.

 

[Peo Sjoblom]

=INDEX(C2:C100,MATCH(1,(A2:A100=21463)*(B2:B100=1),0))

entered with ctrl + shift & enter

Or since you have numbers in the 3rd column

=SUMPRODUCT(--(A2:A100=21463),--(B2:B100=1),C2:C100)

the latter will actually sum the 3rd column if there are more than one match
and it won't work if you need text values in a 3rd column


Also, instead of using 21463 and 1 etc you should use a cell for each where
you would type these
criteria that way you don't need to edit the formula when you change the
criteria


=INDEX(C2:C100,MATCH(1,(A2:A100=D1)*(B2:B100=D2),0))


for example




--


Regards,


Peo Sjoblom