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