Excel 2007 : Function to enter data in 2 columns and get data in 3rd Column automatically

Try this in C18:

=SUMPRODUCT(($A$2:$A$12=A18)*($B$2:$B$12=B18),$C$2:$C$12) and copy down.

Originally Posted by somali.cc

Hello Soren, is there any way that the above problem cud be solved using Vlookup ?

Firstly; go with Cutter's solution. It's better than my =SUMPRODUCT() alternative.

Secondly, with regards to using =VLOOKUP(), I guess it would be possible. But it would be a bit more complicated than is nescessary, since you have two criteria that need to be fulfilled. I don't see any reason why you wouldn't use =SUMIFS(), but perhaps I'm missing something?

Duplicate post: http://www.excelforum.com/hello-intr...matically.html

The above is in Introductory section - so just to let everyone know that responses MAY be made there.

Hi Somali,

If you need vlookup solution, you can use below one for year:-

=VLOOKUP($A$18,$A$1:$C$14,2,0)

and below one for value:-

=VLOOKUP($A$18,$A$1:$C$14,3,0)

Note:- If you have multiple results to be obtained... vlookup will pick up the upper one.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

The way I would do it then, would be to create a "helper" column in the original data, and then in the lookup function use concatenate, but it could probably be done more elegantly.

Okay Somali... share your purpose i.e., type the results manually which you want to see and I'll try to get them using formulas.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi Dilip, The following is my requirement: A function has to be inserted in $C$18 so that ...

If I type ' Office ' in $A$18 and I type ' 2003 ' in $B$18, then $C$18 will automatically dispaly ' 6700 '.

Again, If I type ' Office ' in $A$18 and I type ' 2007 ' in $B$18, then $C$18 will automatically display ' 9000 ' .

The SUMIFS() function suggested by Cutter is working absolutely fine, but we have so far covered the following functions-

IF() , AND(), OR(), NOT(), COUNT(), VLOOKUP(), HLOOKUP(), SUM(), SUMIF(), COUNTBLANK().

Hi Somali,

use the below function :-

{=INDEX($A$2:$C$12,MATCH(A18&B18,$A$2:$A$12&$B$2:$B$12,0),3)}

See the attachment :-
Datasheet.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>