Vlookup with 2 different criteria before inserting a value

  • Thread starter Thread starter Eqa
  • Start date Start date
E

Eqa

I have multiple columns of data. I want Col T to insert a duty rate from a
refernce list depending on the country of supply in Col C and the type of
product in Col I. What formula do I need to enter so that it cross references
these 2 variables.

Thanks in anticipation.

Eqa
 
Eric,

What do I place in logical 1 and logical 2? the cell C1 and I1 or a list of
all the countries and a list of all the product types? And how does it then
distinguish which country and which product gets which duty rate?

Eqa
 
say your duty rate is in col D,
=sumproduct(--(c1:c10="what country?"),--(i1:i10="product type"),--(d1:d10))
change the range to suit your needs.
 
Eric,

I don't think I explained myself clearly.

I'll start again:
Col A has a drop down box with all the countries we import fom,
Col B has a drop down box with all the types of product we import
In Col C which is the duty payable dependent on which country and what type
of product it is a certain duty rate applies.

What do I need to do so that this will understand different rates for the
same product from different countries.Forget that I have a look up list
because aI think this is cinfusing me. So tell me step by step what I need to
establish so that this works.

Hope this is clear and thanks,

Eqa
 
<<<"Forget that I have a look up list because aI think this is cinfusing
me.">>>

You *cannot* forget about this lookup list, because that is the *basis* for
your required information..

Ideally, you could have a datalist of 3 columns, in an out-of-the-way
location, say X, Y, and Z,
where X contains the countries, Y the product type, and Z the duty rate for
the corresponding row combination of country and product.

Say your datalist went from X2 to Z50.
Say your dropdowns started in A2 and B2.

Enter this in C2:

=SUMPRODUCT(($X$2:$X$50=A2)*($Y$2:$Y$50=B2)*$Z$2:$Z$50)

And copy down as needed.
 
Great thanks for that RD.

Eqa

RAGdyer said:
<<<"Forget that I have a look up list because aI think this is cinfusing
me.">>>

You *cannot* forget about this lookup list, because that is the *basis* for
your required information..

Ideally, you could have a datalist of 3 columns, in an out-of-the-way
location, say X, Y, and Z,
where X contains the countries, Y the product type, and Z the duty rate for
the corresponding row combination of country and product.

Say your datalist went from X2 to Z50.
Say your dropdowns started in A2 and B2.

Enter this in C2:

=SUMPRODUCT(($X$2:$X$50=A2)*($Y$2:$Y$50=B2)*$Z$2:$Z$50)

And copy down as needed.
Click to expand...
 
Hi RagDyeR,

Is the criteria only limited to 2? I tried 3 and it didn't work.

Thanks,
Tweety
 
You can simply add additional ranges.

Just make sure that all the ranges are exactly the same size:

=SUMPRODUCT(($X$2:$X$50=A2)*($Y$2:$Y$50=B2)*($W$2:$W$50=C2)*$Z$2:$Z$50)
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hi RagDyeR,

Is the criteria only limited to 2? I tried 3 and it didn't work.

Thanks,
Tweety
 
What formula did you use?
What exactly does "didn't work" mean?
No answer - wrong answer - error message ?

Remember ... all ranges must be the same size.

Did you use something like this:

=SUMPRODUCT(($W$2:$W$50=C2)*($X$2:$X$50=A2)*($Y$2:$Y$50=B2)*$Z$2:$Z$50)
 
The formula you recommended :
=SUMPRODUCT(($W$2:$W$50=C2)*($X$2:$X$50=A2)*($Y$2:$Y$50=B2)*$Z$2:$Z$50)

It returns "0" instead of "456" which is suppose to be the right answer.

How many criteria can the formula allow??

Thanks!!
 
Hi,

I make myself clearer:
A B C D E F G
110 3 2A 1777 3 3000 456
111 4 5A 2587 2 3156 12.65
112 5 2A 3397 1 3312 -430.7
Put my criteria in column I, J and K:

Criteria 1 Criteria 2 Criteria 3 Value return from col H
110 2A 3000 0

The formula I use in column L "Value return from col H" is:
=SUMPRODUCT(($A$2:$A$11=I2)*($C$2:$C$11=J2)*($F$2:$F$11=K2)*$G$2:$G$11)

but the return value is "0".

Please enlighten. Thank you.
 
Your formula works fine for me. However, it will only work if each of the
lookup values and the corresponding data are of the same type (number or
text). For example, the 110 in column 2 and the 110 in column I must both be
numbers or both text. I suggest you use a formula such as =ISTEXT(I2)
(altering I2 to reference each cell in turn) to check this.
 
If *you* copy your data from this thread, and paste it into a new sheet,
you'll see that the formula you're using works perfectly.

Since your using the asterisk form of Sumproduct(), that means the format of
Column G doesn't matter, as long as it looks like a number, it will
calculate.

That leaves Columns A and F ... and cells I2 and K2.

Column A and I2 must be the same!
Column F and K2 must be the same!

Manually key them in as a test, and I'll bet your formula will work.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi,

I make myself clearer:
A B C D E F G
110 3 2A 1777 3 3000 456
111 4 5A 2587 2 3156 12.65
112 5 2A 3397 1 3312 -430.7
Put my criteria in column I, J and K:

Criteria 1 Criteria 2 Criteria 3 Value return from col H
110 2A 3000 0

The formula I use in column L "Value return from col H" is:
=SUMPRODUCT(($A$2:$A$11=I2)*($C$2:$C$11=J2)*($F$2:$F$11=K2)*$G$2:$G$11)

but the return value is "0".

Please enlighten. Thank you.
 
I should have added:
Make sure the cells have the same format before keying in values.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

If *you* copy your data from this thread, and paste it into a new sheet,
you'll see that the formula you're using works perfectly.

Since your using the asterisk form of Sumproduct(), that means the format of
Column G doesn't matter, as long as it looks like a number, it will
calculate.

That leaves Columns A and F ... and cells I2 and K2.

Column A and I2 must be the same!
Column F and K2 must be the same!

Manually key them in as a test, and I'll bet your formula will work.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi,

I make myself clearer:
A B C D E F G
110 3 2A 1777 3 3000 456
111 4 5A 2587 2 3156 12.65
112 5 2A 3397 1 3312 -430.7
Put my criteria in column I, J and K:

Criteria 1 Criteria 2 Criteria 3 Value return from col H
110 2A 3000 0

The formula I use in column L "Value return from col H" is:
=SUMPRODUCT(($A$2:$A$11=I2)*($C$2:$C$11=J2)*($F$2:$F$11=K2)*$G$2:$G$11)

but the return value is "0".

Please enlighten. Thank you.
 
Back
Top