Return value in 3rd column based on 2 other columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joe M. - 29 May 2008 16:39 GMT

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.

Reply to this Message

dhstein - 29 May 2008 16:51 GMT

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.

> 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.
[quoted text clipped - 11 lines]
> Thanks,
> Joe M.

Reply to this Message

Peo Sjoblom - 29 May 2008 16:52 GMT

=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

Signature

Regards,

Peo Sjoblom

>I have a worksheet with 3 columns. I would like to return the value of
>column
[quoted text clipped - 13 lines]
> Thanks,
> Joe M.

Reply to this Message