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

#### garychaan

##### New Member
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?

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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?

=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.

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.

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

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

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

garychaan.xls
ABCDEFG
1Field1Field2Field3Field4a011203418
2a011203445
3a011203463
4a021203453
5a021102524
6a031102534
Sheet2

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.

Thanks and kinds regards.

Sorry to pick on you again.

Thanks.

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

Sorry to pick on you again.

Thanks.

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

Replies
1
Views
117
Replies
1
Views
248
Replies
3
Views
155
Replies
5
Views
198
Replies
4
Views
375

1,218,514
Messages
6,142,910
Members
450,453
Latest member
spaudel

### 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.

### Which adblocker are you using?

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

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