VLOOKUP Function

Can you enter multiple values in the "Lookup_Value". I have 3 worksheet, on
sheet 1, I have cell J9 that looks at I9 and a data table on sheet 3 which
works ok for now, but the results in J9 must be in conjunction with I9 & D9.
Can anyone help with this request.
Thanks in advance for reading my posting.

Vlookup can't take multiple criteria. You could create a helper column with
the criteria columns concatenated (in K9, for instance: =I9 & "-" & D9) and
lookup the concatenated values. Or check
http://www.xldynamic.com/source/xld.SUMPRODUCT.html to see how the sumproduct
function might help.

"Serge" wrote:

> Can you enter multiple values in the "Lookup_Value". I have 3 worksheet, on
> sheet 1, I have cell J9 that looks at I9 and a data table on sheet 3 which
> works ok for now, but the results in J9 must be in conjunction with I9 & D9.
> Can anyone help with this request.
> Thanks in advance for reading my posting.
>
>

You're trying to match up on multiple columns and when all the columns match,
then retrieve a value from that same row???

You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
(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't use the whole column.

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

(still an array formula)

Serge wrote:
>
> Can you enter multiple values in the "Lookup_Value". I have 3 worksheet, on
> sheet 1, I have cell J9 that looks at I9 and a data table on sheet 3 which
> works ok for now, but the results in J9 must be in conjunction with I9 & D9.
> Can anyone help with this request.
> Thanks in advance for reading my posting.
>

--

Dave Peterson

Thanks for your reply Dave, it's 7:30 pm I will try this tomorrow evening
after work. I'm too tired at the moment. I've been at this for the last 8
hours.

"Dave Peterson" wrote:

> You're trying to match up on multiple columns and when all the columns match,
> then retrieve a value from that same row???
>
> You can use this kind of syntax:
>
> =index(othersheet!$c$1:$c$100,
> match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
> (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't use the whole column.
>
> 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))
>
> (still an array formula)
>
> Serge wrote:
> >
> > Can you enter multiple values in the "Lookup_Value". I have 3 worksheet, on
> > sheet 1, I have cell J9 that looks at I9 and a data table on sheet 3 which
> > works ok for now, but the results in J9 must be in conjunction with I9 & D9.
> > Can anyone help with this request.
> > Thanks in advance for reading my posting.
> >
>
> --
>
> Dave Peterson
>