software

Can you enter multiple values in the quot;Lookup_Valuequot;. 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 amp; 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 amp; quot;-quot; amp; D9) and
lookup the concatenated values. Or check
www.xldynamic.com/source/xld.SUMPRODUCT.html to see how the sumproduct
function might help.

quot;Sergequot; wrote:

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

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:
gt;
gt; Can you enter multiple values in the quot;Lookup_Valuequot;. I have 3 worksheet, on
gt; sheet 1, I have cell J9 that looks at I9 and a data table on sheet 3 which
gt; works ok for now, but the results in J9 must be in conjunction with I9 amp; D9.
gt; Can anyone help with this request.
gt; Thanks in advance for reading my posting.
gt;

--

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.

quot;Dave Petersonquot; wrote:

gt; You're trying to match up on multiple columns and when all the columns match,
gt; then retrieve a value from that same row???
gt;
gt; You can use this kind of syntax:
gt;
gt; =index(othersheet!$c$1:$c$100,
gt; match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
gt; (one cell)
gt;
gt; This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
gt; correctly, excel will wrap curly brackets {} around your formula. (don't type
gt; them yourself.)
gt;
gt; Adjust the range to match--but you can't use the whole column.
gt;
gt; This returns the value in othersheet column C when column A and B (of
gt; othersheet) match A2 and B2 of the sheet with the formula.
gt;
gt; And you can add more conditions by just adding more stuff to that product
gt; portion of the formula:
gt;
gt; =index(othersheet!$d$1:$d$100,
gt; match(1,(a2=othersheet!$a$1:$a$100)
gt; *(b2=othersheet!$b$1:$b$100)
gt; *(c2=othersheet!$c$1:$c$100),0))
gt;
gt; (still an array formula)
gt;
gt; Serge wrote:
gt; gt;
gt; gt; Can you enter multiple values in the quot;Lookup_Valuequot;. I have 3 worksheet, on
gt; gt; sheet 1, I have cell J9 that looks at I9 and a data table on sheet 3 which
gt; gt; works ok for now, but the results in J9 must be in conjunction with I9 amp; D9.
gt; gt; Can anyone help with this request.
gt; gt; Thanks in advance for reading my posting.
gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;