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

[somali.cc]

Hello, I am new to Excel. In the Reference Function chapter (Vlookup,Hlookup, etc), there's a question which I have attached here.

Pls help me .

[Søren Larsen]

Try this in C18:

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

[Cutter]

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.

[somali.cc]

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

[Cutter]

It can be solved with SUMIFS() (because there are no duplicate combinations)

=SUMIFS($C$2:$C$12,$A$2:$A$12,A18,$B$2:$B$12,B18)

[dilipandey]

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>

[Søren Larsen]

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?

[Cutter]

I think it might be a requirement of the assignment.

[somali.cc]

Yes Cutter, u r right. We have not yet been taught the functions like ' SUMIFS' and 'SUMPRODUCT' .

The assignment has been given under Lookup and Reference Functions Topic.

[Søren Larsen]

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.

[somali.cc]

Dilip thanx for ur reply, but Vlookup format u suggested isnot serving the purpose.

[dilipandey]

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>

[somali.cc]

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().

[dilipandey]

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>

[Cutter]

@dilipandey

I think you're missing the point here. This is homework and OP can't use a formula that hasn't been taught yet. I wouldn't have posted a solution had I realized at the time that it was homework.