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

garychaan

New Member
Joined
Jun 29, 2002
Messages
27
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Jun 29, 2002
Messages
27
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

New Member
Joined
Jun 29, 2002
Messages
27
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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
ABCDEFG
1Field1Field2Field3Field4a011203418
2a011203445
3a011203463
4a021203453
5a021102524
6a031102534
Sheet2
 

garychaan

New Member
Joined
Jun 29, 2002
Messages
27
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,144,765
Messages
5,726,164
Members
422,659
Latest member
RGP268

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top