RE: How do I put multiple criteria in LOOKUP formulas
Martin Fishlock
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,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
Dave Peterson
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,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
--
Dave Peterson
Jai A
Very helpfull
Cheers
jai
Jai A
Very helpfull
Cheers
Jai