How do I put multiple criteria in LOOKUP formulas

[Jai A]

Is it possible to enter multiple criteria in formulas.
e.g. something like this =DGET(Actual_Volumes,"Gate","Month"=C1&"Week"=D5)
or =VLOOKUP("Month"=C1&"Week"=D5,Actual_Volumes,2,FALSE).
I need to extract data from a table with 2 or more criteria. I know I could
set up range adresses with the criteria but was hoping a simple AND/OR in the
formula line would suffice. Any help much appreciated

 

[Martin Fishlock]

Hi Jai:

Vlookup does not allow multiple lookup keys, what you can do is combine the
lookup keys to make unique lookup keys. So in the datatable add a new column
to the left somewhere and do

=month&":"&week where month and week are specific columns.

Then in the vlookup do

=VLOOKUP(c1&":"&D5,Actual_Volumes,2,FALSE).
adjusting for the new column. Be sure to put a seperator in between to make
sure you don't get problems with the keys repeating.

You can also use the sumproduct as in the following:

=sumproduct(--(a!A1:A5=c1),--(a!B1:B5=d5),(a!c1:c5))

if you are sure that you have uniqueness (no repeating items.

the sumproduct is the easiest method although it can be a little slow.

 

[Dave Peterson]

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))

 

[Jai A]

Thanks Dave,
Very helpfull
Cheers
Jai

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))

 

[Jai A]

Thanks Martin,
Very helpfull
Cheers
jai

Hi Jai:

Vlookup does not allow multiple lookup keys, what you can do is combine the
lookup keys to make unique lookup keys. So in the datatable add a new column
to the left somewhere and do

=month&":"&week where month and week are specific columns.

Then in the vlookup do

=VLOOKUP(c1&":"&D5,Actual_Volumes,2,FALSE).
adjusting for the new column. Be sure to put a seperator in between to make
sure you don't get problems with the keys repeating.

You can also use the sumproduct as in the following:

=sumproduct(--(a!A1:A5=c1),--(a!B1:B5=d5),(a!c1:c5))

if you are sure that you have uniqueness (no repeating items.

the sumproduct is the easiest method although it can be a little slow.