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 Function
 

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.

VLOOKUP Function
 

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

VLOOKUP Function
 

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