Vlookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kam - 22 Sep 2007 14:42 GMT

Can you please help to get correct Vlookup function.

I have two huge Worksheet which has 45000 lines on each sheet. I want to
look up one value in both the sheet. If look value in not found in Sheet1
then go to Sheet2 & get the out from there.

Please let me know if this is clear to you.

Reply to this Message

FC - 22 Sep 2007 15:52 GMT

Wouldn't it help better trying : Ctrl+F > options > entire workbook, then
writting the value and click "find all" ?

> Can you please help to get correct Vlookup function.
>
[quoted text clipped - 3 lines]
>
> Please let me know if this is clear to you.

Reply to this Message

Gary''s Student - 22 Sep 2007 16:06 GMT

In sheet1 A1 thru B12

0    mouse
1    mouse
2    cat
3    cat
4    cat
5    dog
6    cat
7    cat
8    cat
9    cat
10    dog
11    dog

in sheet2 A1 thru B13:

0    mouse
1    mouse
2    cat
3    cat
4    cat
5    dog
6    cat
7    cat
113    cat
9    cat
10    dog
11    dog
13    ant

in sheet1 C2 thru C4:

=VLOOKUP(C1,A1:B12,2,FALSE)
=VLOOKUP(C1,Sheet2!A1:B13,2,FALSE)
=IF(ISERROR(C2),C3,C2)

so if we put 5 in C1 we will see: dog
and if we put 13 in C1 we will see:ant

Signature

Gary''s Student - gsnu200746

> Can you please help to get correct Vlookup function.
>
[quoted text clipped - 3 lines]
>
> Please let me know if this is clear to you.

Reply to this Message

eliano - 23 Sep 2007 23:48 GMT

Hi Kam.

Following the good indication of our friend Gary (HI), try:
=SE(CONTA.SE(A1:A13;C1)>0;CERCA.VERT(C1;A1:B12;2;FALSO);CERCA.VERT(C1;Foglio2!A1:B13;2;FALSO))

with the translation, for wich I am not sure:
=IF(COUNTIF(A1:A13,C1)>0;VLOOKUP(C1,A1:B12,2,FALSE);VLOOKUP(C1,Sheet2!A1:B13,2,FALSE))
that use only one column.

Regards,
Eliano

> Can you please help to get correct Vlookup function.
>
[quoted text clipped - 3 lines]
>
> Please let me know if this is clear to you.

Reply to this Message

Kam - 24 Sep 2007 10:26 GMT

Sorry...it didnot help me...Or may be I have used in proper way.

See I will give you proper e.g..

Sheet 1(Main Data):
InvoiceRef    BLNo    CustomerCode    CustomerName
LDN1095699    SJ1528553    130GLA16122    FOXTEQ UK LTD
LDN0506799    SJ1398799    130GLA7882    DHL DANZAS
LDN1352394    502651234    130GLA2698    TEXTILE CONCEPT
LDN1350111    503048824    130GLA2405    DIAGEO SCOTLAND LTD
LDN1532590    504339428    130GLA2405    DIAGEO SCOTLAND LTD

Sheet 2 :
InvoiceRef    BLNo    CustomerCode    CustomerName
LDN1095699    SJ1528553    130GLA16122    FOXTEQ UK LTD
LDN0506799    SJ1398799    130GLA7882    DHL DANZAS

Sheet 3 :
InvoiceRef    BLNo    CustomerCode    CustomerName
LDN1352394    502651234    130GLA2698    TEXTILE CONCEPT
LDN1350111    503048824    130GLA2405    DIAGEO SCOTLAND LTD
LDN1532590    504339428    130GLA2405    DIAGEO SCOTLAND LTD

Now if you compare Sheet2 & Sheet3 data with "Main Data(Sheet1)" there some
data in Sheet2 & some of them are in Sheet3.

So I need formula which can look values from Main Data into Sheet2 & Sheet3
& give me proprt output...

Appreciate your help.

Best Regards,
Kam.

> Can you please help to get correct Vlookup function.
>
[quoted text clipped - 3 lines]
>
> Please let me know if this is clear to you.

Reply to this Message

eliano - 24 Sep 2007 16:06 GMT

> Sorry...it didnot help me...Or may be I have used in proper way.
>
[quoted text clipped - 35 lines]
> > look up one value in both the sheet. If look value in not found in Sheet1
> > then go to Sheet2 & get the out from there.

I believe that is clear, however look in the help on line the function
HLOOKUP, thanks.

The formula, written in Sheet1, look for the data in Sheet2 or, in not
found, in Sheet3.
This formula, in XL2000 Italian find the CustomerName (the 4st element)
be kind enough to change the riferiments in accord with your ranges.
=SE(CONTA.SE(Foglio2!$A$2:$A$13;$A2)=1;CERCA.VERT($A2;Foglio2!$A$2:$D$13;4;FALSO);CERCA.VERT($A2;Foglio3!$A$2:$D$13;4;FALSO))

traslated in English, i believe is:
=IF(COUNTIF(Sheet2!$A$2:$A$13,$A2)=1;VLOOKUP($A2,Sheet2!$A$2:$D$13,4,FALSE);VLOOKUP($A2,Sheet3$A$2:$D$13,4,FALSE))

Regards
Eliano

Reply to this Message

Kam - 24 Sep 2007 16:34 GMT

Thanks it worked now....Your help is really appreciated.

Best Regards,
Kam.

> > Sorry...it didnot help me...Or may be I have used in proper way.
> >
[quoted text clipped - 50 lines]
> Regards
> Eliano

Reply to this Message