# VLOOKUP Function

1. ## 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.

2. ## RE: 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.
>
>

3. ## Re: 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.
>

--

Dave Peterson

4. ## Re: 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.
> >

>
> --
>
> Dave Peterson
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1