VLOOKUP and logical functions

Hi

I'm trying to do a VLOOKUP from two different sheets on a another workbook.
Can a person combine say for instance a logical function like OR together
with VLOOKUP?

E.g.
=OR(VLOOKUP(lookup_value,'[Register.xls]A3'!table_array,col_index_num,FALSE,VLOOKUP(lookup_value,'[Register.xls]K'!table_array,col_index_num,FALSE))

I've tried this, but it doesn't seem to work. I'm not sure what other
functions one could try to achieve this.

What are you trying to do ... explain your logic.

You have "lookup_value" and cells A3 and K ... is the latter a typo? Are
trying to lookup using A3 OR K..?

You probably need an IF statement to determine which one to use.

"Margherita" wrote:

> Hi
>
> I'm trying to do a VLOOKUP from two different sheets on a another workbook.
> Can a person combine say for instance a logical function like OR together
> with VLOOKUP?
>
> E.g.
> =OR(VLOOKUP(lookup_value,'[Register.xls]A3'!table_array,col_index_num,FALSE,VLOOKUP(lookup_value,'[Register.xls]K'!table_array,col_index_num,FALSE))
>
> I've tried this, but it doesn't seem to work. I'm not sure what other
> functions one could try to achieve this.

=IF(ISNA(vlookup1),IF(ISNA(vlookup2),"",vlookup2),vlookup1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Margherita" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> I'm trying to do a VLOOKUP from two different sheets on a another
workbook.
> Can a person combine say for instance a logical function like OR together
> with VLOOKUP?
>
> E.g.
>
=OR(VLOOKUP(lookup_value,'[Register.xls]A3'!table_array,col_index_num,FALSE,
VLOOKUP(lookup_value,'[Register.xls]K'!table_array,col_index_num,FALSE))
>
> I've tried this, but it doesn't seem to work. I'm not sure what other
> functions one could try to achieve this.

Its not clear what you require, if you wish to lookup from 2 places can the item exist in both arrays? If it can only exist in one array

if(isna(firstloookup),secondlookup,firstlookup)

Assuming that the value must exist in one of the 2 arrays

but you need to provide more information as to what you are trying to achieve. Are you returning numbers or text?

regards

Dav

I have a drawing register (Register.xls) with part numbers on, parts
beginning with A3* is on a sheet named "A3", then all parts beginning with K*
is on a sheet named "K".

Normally the VLOOKUP command works when I specify the table of array on one
sheet, e.g. sheet A3. No problem. (I use the VLOOKUP command when I create a
parts list in a drawing, where I only need specific information relating to
that part e.g. material)

What I need to know is if you can you make it lookup on sheet "A3" and sheet
"K"? And how?

Let's say for example I need to find out material for part number K31RR0002A
(which is specified on sheet "K" in my register), but because my VLOOKUP
command only reads the table of array on sheet "A3", it doesn't show up the
value because the part is specified on a different sheet. But if I look up a
value for part number A31RR0002A, then I get the correct answer (because A3*
is specified on sheet A3). I need it to be able to lookup a value on two
different sheets at the same time.

Does it make sense?


"Toppers" wrote:

> What are you trying to do ... explain your logic.
>
> You have "lookup_value" and cells A3 and K ... is the latter a typo? Are
> trying to lookup using A3 OR K..?
>
> You probably need an IF statement to determine which one to use.
>
> "Margherita" wrote:
>
> > Hi
> >
> > I'm trying to do a VLOOKUP from two different sheets on a another workbook.
> > Can a person combine say for instance a logical function like OR together
> > with VLOOKUP?
> >
> > E.g.
> > =OR(VLOOKUP(lookup_value,'[Register.xls]A3'!table_array,col_index_num,FALSE,VLOOKUP(lookup_value,'[Register.xls]K'!table_array,col_index_num,FALSE))
> >
> > I've tried this, but it doesn't seem to work. I'm not sure what other
> > functions one could try to achieve this.

Hi Dav

Thanks a lot, this cleared it up for me now. It works! Yippeeee!!

Thank you, thank you, thank you!

"Dav" wrote:

>
> Its not clear what you require, if you wish to lookup from 2 places can
> the item exist in both arrays? If it can only exist in one array
>
> if(isna(firstloookup),secondlookup,firstlookup)
>
> Assuming that the value must exist in one of the 2 arrays
>
> but you need to provide more information as to what you are trying to
> achieve. Are you returning numbers or text?
>
> regards
>
> Dav
>
>
> --
> Dav
> ------------------------------------------------------------------------
> Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
> View this thread: http://www.excelforum.com/showthread...hreadid=573759
>
>