Vlookup

K

Kam

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.
 
F

FC

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

Gary''s Student

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
 
E

eliano

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
 
K

Kam

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.
 
E

eliano

Kam said:
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.
Click to expand...

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
 
Top