|
Vlookup
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. |
Vlookup
Wouldn't it help better trying : Ctrl+F options entire workbook, then
writting the value and click "find all" ? "Kam" wrote: 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. |
Vlookup
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 -- Gary''s Student - gsnu200746 "Kam" wrote: 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. |
Vlookup
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;F ALSO);CERCA.VERT(C1;Foglio2!A1:B13;2;FALSO)) with the translation, for wich I am not su =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 "Kam" wrote: 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. |
Vlookup
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. "Kam" wrote: 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. |
Vlookup
"Kam" wrote: 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. "Kam" wrote: 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. 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;Fog lio3!$A$2:$D$13;4;FALSO)) traslated in English, i believe is: =IF(COUNTIF(Sheet2!$A$2:$A$13,$A2)=1;VLOOKUP($A2,S heet2!$A$2:$D$13,4,FALSE);VLOOKUP($A2,Sheet3$A$2:$ D$13,4,FALSE)) Regards Eliano |
Vlookup
Thanks it worked now....Your help is really appreciated.
Best Regards, Kam. "eliano" wrote: "Kam" wrote: 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. "Kam" wrote: 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. 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;Fog lio3!$A$2:$D$13;4;FALSO)) traslated in English, i believe is: =IF(COUNTIF(Sheet2!$A$2:$A$13,$A2)=1;VLOOKUP($A2,S heet2!$A$2:$D$13,4,FALSE);VLOOKUP($A2,Sheet3$A$2:$ D$13,4,FALSE)) Regards Eliano |
| All times are GMT +1. The time now is 04:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com