VLOOKUP and logical functions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Margherita - 21 Aug 2006 14:55 GMT

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.

Reply to this Message

Bob Phillips - 21 Aug 2006 15:09 GMT

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

Signature

HTH

Bob Phillips

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

> Hi
>
[quoted text clipped - 3 lines]
>
> 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.

Reply to this Message

Toppers - 21 Aug 2006 15:10 GMT

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.

> Hi
>
[quoted text clipped - 7 lines]
> I've tried this, but it doesn't seem to work. I'm not sure what other
> functions one could try to achieve this.

Reply to this Message

Margherita - 21 Aug 2006 15:35 GMT

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?

> What are you trying to do ... explain your logic.
>
[quoted text clipped - 14 lines]
> > I've tried this, but it doesn't seem to work. I'm not sure what other
> > functions one could try to achieve this.

Reply to this Message

Dav - 21 Aug 2006 15:20 GMT

Its not clear what you require, if you wish to lookup from 2 places ca
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 t
achieve. Are you returning numbers or text?

regards

Da

Reply to this Message

Margherita - 21 Aug 2006 15:47 GMT

Hi Dav

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

Thank you, thank you, thank you!

> 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
[quoted text clipped - 9 lines]
>
> Dav

Reply to this Message