VLOOKUP problem

[top00001]

Hi. I don't know what kind of Function i should use on my data. Please help me on this:
Sheet 1
A B C D E
1 MONDAY JUNE 25 30 50
2 MONDAY JULY 15 10 45
3 MONDAY AUGUST 11 35 60
4
5

Sheet 2
input: MONDAY
input: JULY

RESULT:

A B C
15 10 45

On the example above, SHEET 2 is the one i want to be the result.. so I will only input the word "MONDAY"
and "JULY" then the result will be 15 10 45.

Hope someone can help me on this.. thanks

[WinteE]

=sumproduct((a1:a3="monday")*(b1:b3="july")*(c1:c3))

[TMS]

See the attached example


Regards, TMS

[TMS]

WinteE's solution is nicer, avoiding the Array Formula.


See attached updated example.


Regards, TMS

[top00001]

WinteE's solution is nicer, avoiding the Array Formula.


See attached updated example.


Regards, TMS

yes this one works but what if for example i want the 15 10 45 to be put on COLUMN E, COL F, COL G? Because when i tried to move the formula on column F, it gives me "#REF!"...

[TMS]

We have both made assumptions based on the simplistic example you have provided. Both solutions address the question posed in a hypothetical scenario.

If you want a solution that matches your real life situation, you would do better to post a sample workbook with some typical data and formula.


Regards, TMS

[WinteE]

The =SUMPRODUCT() in the example of TMS is fixed. Wherever you cut/paste this formula, the results will be the same.

[top00001]

We have both made assumptions based on the simplistic example you have provided. Both solutions address the question posed in a hypothetical scenario.

If you want a solution that matches your real life situation, you would do better to post a sample workbook with some typical data and formula.


Regards, TMS

oh ok, but i am just a newbie here and i really don't have much experience on asking questions on a forum like this.. and i really don't have idea on how formula works on excel so i thought the formula that you gave me might also work on other situation.. but if you don't want to answer my question anymore that's fine.

thanks anyway!!

[TMS]

If you select the cell and copy and paste it, the cell references will adjust. You need to press F2 to edit the formula, select all the text in the formula box and copy it, then press Cancel.. Now go to the cell where you want the formula and paste it.

Regards, TMS

[top00001]

ok.. thanks a lot to both of you guys!!

[TMS]

You're welcome.

[WinteE]

It's not that we don't want to answer you.

You have to learn to 'read' the formula.

=sumproduct((a1:a3="monday")*(b1:b3="july")*(c1:c3))

a1:a3 stands for range a1 till a3. Where it is MONDAY it is TRUE, value = 1. Otherwise the value is 0.
b1:b3 stands for range b1 till b3. Where it is JULY it is TRUE, value = 1. Otherwise the value is 0.
c1:c3 stands for range c1 till c3. This is the column your searching the value, in this case that'll be 15.

=sumproduct( 1 * 1 * 15 ) is 15 . On row 1 it would be = sumproduct( 1 * 0 * 10 ) = 0. Because JUNE is not equal to JULY.

[top00001]

It's not that we don't want to answer you.

You have to learn to 'read' the formula.

=sumproduct((a1:a3="monday")*(b1:b3="july")*(c1:c3))

a1:a3 stands for range a1 till a3. Where it is MONDAY it is TRUE, value = 1. Otherwise the value is 0.
b1:b3 stands for range b1 till b3. Where it is JULY it is TRUE, value = 1. Otherwise the value is 0.
c1:c3 stands for range c1 till c3. This is the column your searching the value, in this case that'll be 15.

=sumproduct( 1 * 1 * 15 ) is 15 . On row 1 it would be = sumproduct( 1 * 0 * 10 ) = 0. Because JUNE is not equal to JULY.

I have idea on how to use the sumproduct but the one TMS gave me was something new to me so i don't understand the functions used on it.. yeah, i thought it would be the same as sumproduct that you can copy/paste on different cell..