Vlookup & Hlookup formulas require..... how?

[garychaan]

I have two columns which house values and two columns which house numeric numbers. They are residing in different cells addresses. Example are as follows:

A B C D
024123 3999 100 -400
039102 3410 200 -100
042101 3050 300 0

I want to sum up column 'C' and 'D' (i.e. value) if the criteria in column 'A' and 'B' (i.e. number) are met.

I think vlookup and hlookup formulas are required here. Can anyone suggest how to go about it?

Please help! Thanks in advance.

 

[Aladin Akyurek]

On 2002-09-18 23:09, garychaan wrote:

I have two columns which house values and two columns which house numeric numbers. They are residing in different cells addresses. Example are as follows:

A B C D
024123 3999 100 -400
039102 3410 200 -100
042101 3050 300 0

I want to sum up column 'C' and 'D' (i.e. value) if the criteria in column 'A' and 'B' (i.e. number) are met.

I think vlookup and hlookup formulas are required here. Can anyone suggest how to go about it?

Please help! Thanks in advance.

=SUMPRODUCT(($A$2:$A$10=E1)*($B$2:$B$10=F1)*($C$2:$D$10))

where E1 and F1 house criteria that must hold for the A-range and the B-range.

 

[garychaan]

Hi Aladin

I did not work. I got '0' value all the way.

For your information, column A and B are a set of number code and it's unique.

BTW, I have e-mail the WB to you before but till now there is no reply.

Hope you could look into it and reply soon.

Any advice and assistance would be appreciated.

Thanks in advance.

 

[garychaan]

Hi Aladin

It's my mistake! The column 'A' and 'B' consist of text and NOT numeric value as mentioned.

Please advice can I still use SUMPRODUCT formula here.

Thanks in advance.

 

[Aladin Akyurek]

On 2002-09-19 00:01, garychaan wrote:

Hi Aladin

It's my mistake! The column 'A' and 'B' consist of text and NOT numeric value as mentioned.

Please advice can I still use SUMPRODUCT formula here.

Thanks in advance.

garychaan.xls
	A	B	C	D	E	F	G
1	Field1	Field2	Field3	Field4	a01	12034	18
2	a01	12034	4	5
3	a01	12034	6	3
4	a02	12034	5	3
5	a02	11025	2	4
6	a03	11025	3	4
Sheet2

 

[garychaan]

Hi Aladin

It works this time. Thanks alot.

I forgot to inform you that columns
'C', 'D', 'E' and etc. represent calender months (all in 12 months). The column that I want to pull changes each month depending on the month that I have selected for. For example, if I select for Aug 2002, I want the vlookup formula to pull text value for the month of Aug 2002 only.

All the text values are housed in separate cell addresses.

The Vlookup formula should have at least three criterias for pulling the text value.
Criteria: (1) Text 1 in column 'A' (2) Text 2 in column 'B' and (3) Months in column 'C' to 'N'

Can you show and explain how would you pull text value using Vlookup/or Hlookup formulas in the above example.

Appreciate your help always.

Thanks and kinds regards.

 

[garychaan]

Aladin

Please help out.....

Sorry to pick on you again.

Thanks.

 

[Aladin Akyurek]

On 2002-09-19 05:02, garychaan wrote:

Aladin

Please help out.....

Sorry to pick on you again.

Thanks.

Please be a bit patient. I didn't have time to look at your WB yet.