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,FAL SE).
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

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.


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Jai A" wrote:

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,FAL SE).
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

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 wrote:

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,FAL SE).
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

--

Dave Peterson

Thanks Dave,
Very helpfull
Cheers
Jai

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

Jai A wrote:

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,FAL SE).
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

--

Dave Peterson

Thanks Martin,
Very helpfull
Cheers
jai

"Martin Fishlock" wrote:

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.


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Jai A" wrote:

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,FAL SE).
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