VLOOKUP & 2 arguments

[kimha]

How do you perform VLOOKUP with 2 arguments? The 2 arguments are listed in
separate fields within the reference table, columns 1 & 2. When both are
met, I want to return the value in column 3.

 

[Ms-Exl-Learner]

Provide some examplse.

 

[Glenn]

How do you perform VLOOKUP with 2 arguments? The 2 arguments are listed in
separate fields within the reference table, columns 1 & 2. When both are
met, I want to return the value in column 3.

Try this:

http://www.contextures.com/xlFunctions01.html#SumProduct

 

[Jacob Skaria]

Kimha

Try the below...which returns the value from col C if both condition 1 and
condition2 agrees.

=INDEX(C1:C10,MATCH(1,(A1:A10=criteria1)*(B1:B10=criteria2),0))

Please note that this is an array formula. An array formula can perform
multiple calculations and then return either a single result or multiple
results. Array formulas act on two or more sets of values known as array
arguments. Each array argument must have the same number of rows and columns.
You create array formulas in the same way that you create other formulas,
except you press CTRL+SHIFT+ENTER to enter the formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula>}"

If this post helps click Yes

 

[kimha]

Pop Year - mo Value
TN 2007-01 88
TN 2007-02 243
TN 2007-03 32
TN 2007-04 69
PA 2007-01 82
PA 2007-02 158
PA 2007-03 34
PA 2007-04 61


So, if Pop = TN and YearMo = 2007-03, return value of 32.

I'm sure there's some sort of way to create embedded IF, AND, and VLOOKUP
statements. I just haven't been able to figure it out.

Thanks-

 

[Ms-Exl-Learner]

Try this...

=SUMPRODUCT(--(A2:A9="TN"),--(B2:B9="2007-03"),--(C2:C9))

Instead of mentioning the value in the formula you can refer it to a cell
(i.e.) "TN" to E2 and the Year "2007-03" to F2 cell

=SUMPRODUCT(--(A2:A9=E2),--(B2:B9=F2),--(C2:C9))

If this post helps, Click Yes!

 

[Jacob Skaria]

The SUMPRODUCT formula is used to sum the 3rd column based on the matching
critera of 1st and 2nd column. In this case it might be OK since you dont
have duplicate values...Try out with text values in 3rd column

If this post helps click Yes

 

[Ken]

kimha

Sometimes it is easier to add a helper column that concatenates the
values in the two columns on which you are conditioning your VLOOKUP.
If you add a column A with the formula =A2&B2 copied down all your
data rows and you have your "lookup conditions" in B10 and C10 you can
use

=VLOOKUP(B10&C10, A210,4,false)

You can have your Pop and your Year-mo as variables in B10 and C10 and
you can hide column A.

Good luck.

Ken
Norfolk, Va

 

[Ashish Mathur]

Hi,

Suppose your data is in range C6:E14 (including the header rows). In
C17:E17 enter the same heading as in C6:E6. In C1818, enter TN and
2007-03. In E18, enter the following formula and copy down

=DSUM($C$6:$E$14,E$17,$C$1718)-SUM($E$17:E17)

You may enter more data C1919 onwards

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com