Lookup with multiple criteria
 

I need to look Up one value, but Using multiple variables, so for example
if(a=x and b=y and c=z, use value from C1, I tried doing a nested loop but
there are too many combinations and Excel says there are too many loops, does
someone know an easier way to do this?

Thanks

Lookup with multiple criteria
 

Well, you can create a formula in such a condition using just the if –
else statement as
=if(a=x,if(b=y,if(c=z,C1)))


Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com




--
Chris Bode

Lookup with multiple criteria
 

Hi,

Not clear; are you looking up the value in column C based on columns A and
B? You appear to be saying look up an item in column C based on items in
column C and in B and A - your ranges overlap and will return a circular
reference? Will the result be unique, is there only one item that will have
conditions x and y? If not what do you want to do?

Suppose the items are unique and the the entry you want to find is in column
D and its is a value, not text:

=SUMPRODUCT(--(A1:A10="x"),--(B1:B10="y"),--(C1:C10="z"),D1:D10)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Ivan" wrote:

I need to look Up one value, but Using multiple variables, so for example
if(a=x and b=y and c=z, use value from C1, I tried doing a nested loop but
there are too many combinations and Excel says there are too many loops, does
someone know an easier way to do this?

Thanks

Lookup with multiple criteria
 

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Ivan wrote:

I need to look Up one value, but Using multiple variables, so for example
if(a=x and b=y and c=z, use value from C1, I tried doing a nested loop but
there are too many combinations and Excel says there are too many loops, does
someone know an easier way to do this?

Thanks

--

Dave Peterson

Lookup with multiple criteria
 

The spread sheet that i am doing will ask the user to input several variables
which I will need to use to look up a value. Example,
The conductor needs to be Copper or Aluminum, There will be 1 or more
conductors and the Insulation temperature is 60C, 75C or 90C,
based On this criterial I need to get into the table that has that
information and pull out the value for that criteria. For example if someone
picked copper, 1, and 90C I will have one value for that, if they pick
another combination the result will be different.

Thanks


"Dave Peterson" wrote:

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Ivan wrote:

I need to look Up one value, but Using multiple variables, so for example
if(a=x and b=y and c=z, use value from C1, I tried doing a nested loop but
there are too many combinations and Excel says there are too many loops, does
someone know an easier way to do this?

Thanks

--

Dave Peterson

Lookup with multiple criteria
 

Excel 2007 Pivot Table
Real electrical specs, no formulas:
http://www.mediafire.com/file/hhvvnjlzzgo/03_05_09.xlsx