|
vlookup
Hi, i use this formula to extract some prices (Formula is in B cell)..
(=IF(A8<"";IF(ISNA(VLOOKUP($A8;$J$1:K20;2;FALSE)) ;0;VLOOKUP($A8;$J$1:K20;2;FALSE));"")) ....but i want to extract from "J" and "L". I dont want columns to be one next to the other. My formula extract prices from "J" and "K". And i would like database ("j" and "L") to be in another sheet. This is my actual table (database in the same sheet): A B... ...J K 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 ....but i want to skip "K" and extract from column "J" and "L" like this (database to be in another sheet): A B... (another sheet) ...J K L 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 Thanks in advance. |
vlookup
=IF(A8<"";IF(ISNA(VLOOKUP($A8;Sheet2!$J$1:$L$20;3 ;FALSE));0;VLOOKUP($A8;Sheet2!$J$1:$L$20;3;FALSE)) ;""))
Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: Hi, i use this formula to extract some prices (Formula is in B cell).. (=IF(A8<"";IF(ISNA(VLOOKUP($A8;$J$1:K20;2;FALSE)) ;0;VLOOKUP($A8;$J$1:K20;2;FALSE));"")) ...but i want to extract from "J" and "L". I dont want columns to be one next to the other. My formula extract prices from "J" and "K". And i would like database ("j" and "L") to be in another sheet. This is my actual table (database in the same sheet): A B... ...J K 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 ...but i want to skip "K" and extract from column "J" and "L" like this (database to be in another sheet): A B... (another sheet) ...J K L 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 Thanks in advance. |
vlookup
THANKS ALLOT!!!
You again Stefi..:) Thanks! "Stefi" a scris: =IF(A8<"";IF(ISNA(VLOOKUP($A8;Sheet2!$J$1:$L$20;3 ;FALSE));0;VLOOKUP($A8;Sheet2!$J$1:$L$20;3;FALSE)) ;"")) Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: Hi, i use this formula to extract some prices (Formula is in B cell).. (=IF(A8<"";IF(ISNA(VLOOKUP($A8;$J$1:K20;2;FALSE)) ;0;VLOOKUP($A8;$J$1:K20;2;FALSE));"")) ...but i want to extract from "J" and "L". I dont want columns to be one next to the other. My formula extract prices from "J" and "K". And i would like database ("j" and "L") to be in another sheet. This is my actual table (database in the same sheet): A B... ...J K 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 ...but i want to skip "K" and extract from column "J" and "L" like this (database to be in another sheet): A B... (another sheet) ...J K L 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 Thanks in advance. |
vlookup
You are welcome! Thanks for the feedback!
Stefi €˛puiuluipui€¯ ezt Ć*rta: THANKS ALLOT!!! You again Stefi..:) Thanks! "Stefi" a scris: =IF(A8<"";IF(ISNA(VLOOKUP($A8;Sheet2!$J$1:$L$20;3 ;FALSE));0;VLOOKUP($A8;Sheet2!$J$1:$L$20;3;FALSE)) ;"")) Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: Hi, i use this formula to extract some prices (Formula is in B cell).. (=IF(A8<"";IF(ISNA(VLOOKUP($A8;$J$1:K20;2;FALSE)) ;0;VLOOKUP($A8;$J$1:K20;2;FALSE));"")) ...but i want to extract from "J" and "L". I dont want columns to be one next to the other. My formula extract prices from "J" and "K". And i would like database ("j" and "L") to be in another sheet. This is my actual table (database in the same sheet): A B... ...J K 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 ...but i want to skip "K" and extract from column "J" and "L" like this (database to be in another sheet): A B... (another sheet) ...J K L 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 Thanks in advance. |
vlookup
Stefi, i have another problem.
i have no answer yet, so....you are my last hope :) here are the mesages: (1.) Stefi, can you help me with one last problem? at least i hope so.... It's about date formula... =IF(VLOOKUP(A1;PLANNING!B:C;2;FALSE)=TEXT(B5;"dddd ");"YES";"NO") Example: a b c planning! b c 1 john (ace) 11.05.2008 no john monday 2 mary (mer) 16.05.2008 yes mary friday i would like formula to ignore parenthesis " (ace) ; (mer) " in my table i will write like this :john (ace) and mary (mer), but in database it will be without parenthesis : "john ; mary"... Can formula extract only john or mary? ..and ignore the rest? it is posible? THANKS ALLOT (2.) This will extract the name before (ace) =MID(A1,1,((FIND(" ",A1,1)-1))) edvwvw (3.) i want to use this formula =IF(VLOOKUP(A1;PLANNING!B:C;2;FALSE)=TEXT(B5;"dddd ");"YES";"NO") and extract everithing from cell a1 but without parenthesis. i will try to explain better in my example, and how this formula works for me: a b c PLANNING! b c 1 john 11.05.2008 no john monday 2 mary 16.05.2008 yes mary friday 3 john 12.05.2008 yes the formula tell me if john or mary arrive in the correct day. the problem is thet after john, it will be one or two more words, even numbers example : " john ed (ace)" ; "john 117 (ace)" . so i don't want to extract the first word, i want to exclude the parenthesis. and i wont to use the formula i used before. with a little modification... the new table will look like this: a b c PLANNING! b c 1 john 117 (ace) 11.05.2008 no john monday 2 mary re (mer) 16.05.2008 yes mary friday 3 john ed (ace) 12.05.2008 yes john 117 monday I need to use the same formula and to write in a1 "john 117 (ace)" but the formula to see it as john 117 and to tell me if john it's comming in the correct day. THANKS i hope you can help me. plsssssss thanks Stefi :) "Stefi" a scris: You are welcome! Thanks for the feedback! Stefi €˛puiuluipui€¯ ezt Ć*rta: THANKS ALLOT!!! You again Stefi..:) Thanks! "Stefi" a scris: =IF(A8<"";IF(ISNA(VLOOKUP($A8;Sheet2!$J$1:$L$20;3 ;FALSE));0;VLOOKUP($A8;Sheet2!$J$1:$L$20;3;FALSE)) ;"")) Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: Hi, i use this formula to extract some prices (Formula is in B cell).. (=IF(A8<"";IF(ISNA(VLOOKUP($A8;$J$1:K20;2;FALSE)) ;0;VLOOKUP($A8;$J$1:K20;2;FALSE));"")) ...but i want to extract from "J" and "L". I dont want columns to be one next to the other. My formula extract prices from "J" and "K". And i would like database ("j" and "L") to be in another sheet. This is my actual table (database in the same sheet): A B... ...J K 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 ...but i want to skip "K" and extract from column "J" and "L" like this (database to be in another sheet): A B... (another sheet) ...J K L 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 Thanks in advance. |
vlookup
Maybe you need this formula:
=IF(VLOOKUP(TRIM(LEFT(A2;SEARCH("(";A2)-1));planning!A:B;2;FALSE)=TEXT(B2;"dddd");"YES";"N O") It'll lookup "john ed", "john 117" in planning sheet, so you must have the names in these formats in planning sheet. I don't understand this sentence of yours: "so i don't want to extract the first word, i want to exclude the parenthesis." If you want only john, mary in the planning sheet to be found, you have to extract the first word. In this case replace TRIM(LEFT(A2;SEARCH("(";A2)-1)) by TRIM(LEFT(A2;SEARCH(" ";A2)-1)) in the formula! Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: Stefi, i have another problem. i have no answer yet, so....you are my last hope :) here are the mesages: (1.) Stefi, can you help me with one last problem? at least i hope so.... It's about date formula... =IF(VLOOKUP(A1;PLANNING!B:C;2;FALSE)=TEXT(B5;"dddd ");"YES";"NO") Example: a b c planning! b c 1 john (ace) 11.05.2008 no john monday 2 mary (mer) 16.05.2008 yes mary friday i would like formula to ignore parenthesis " (ace) ; (mer) " in my table i will write like this :john (ace) and mary (mer), but in database it will be without parenthesis : "john ; mary"... Can formula extract only john or mary? ..and ignore the rest? it is posible? THANKS ALLOT (2.) This will extract the name before (ace) =MID(A1,1,((FIND(" ",A1,1)-1))) edvwvw (3.) i want to use this formula =IF(VLOOKUP(A1;PLANNING!B:C;2;FALSE)=TEXT(B5;"dddd ");"YES";"NO") and extract everithing from cell a1 but without parenthesis. i will try to explain better in my example, and how this formula works for me: a b c PLANNING! b c 1 john 11.05.2008 no john monday 2 mary 16.05.2008 yes mary friday 3 john 12.05.2008 yes the formula tell me if john or mary arrive in the correct day. the problem is thet after john, it will be one or two more words, even numbers example : " john ed (ace)" ; "john 117 (ace)" . so i don't want to extract the first word, i want to exclude the parenthesis. and i wont to use the formula i used before. with a little modification... the new table will look like this: a b c PLANNING! b c 1 john 117 (ace) 11.05.2008 no john monday 2 mary re (mer) 16.05.2008 yes mary friday 3 john ed (ace) 12.05.2008 yes john 117 monday I need to use the same formula and to write in a1 "john 117 (ace)" but the formula to see it as john 117 and to tell me if john it's comming in the correct day. THANKS i hope you can help me. plsssssss thanks Stefi :) "Stefi" a scris: You are welcome! Thanks for the feedback! Stefi €˛puiuluipui€¯ ezt Ć*rta: THANKS ALLOT!!! You again Stefi..:) Thanks! "Stefi" a scris: =IF(A8<"";IF(ISNA(VLOOKUP($A8;Sheet2!$J$1:$L$20;3 ;FALSE));0;VLOOKUP($A8;Sheet2!$J$1:$L$20;3;FALSE)) ;"")) Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: Hi, i use this formula to extract some prices (Formula is in B cell).. (=IF(A8<"";IF(ISNA(VLOOKUP($A8;$J$1:K20;2;FALSE)) ;0;VLOOKUP($A8;$J$1:K20;2;FALSE));"")) ...but i want to extract from "J" and "L". I dont want columns to be one next to the other. My formula extract prices from "J" and "K". And i would like database ("j" and "L") to be in another sheet. This is my actual table (database in the same sheet): A B... ...J K 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 ...but i want to skip "K" and extract from column "J" and "L" like this (database to be in another sheet): A B... (another sheet) ...J K L 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 Thanks in advance. |
vlookup
You are the best. it works. But now i have another problem.
You transformed a formula for me and works great, but now if i put "(ace)" next to product, the formula doesn't work anymore. Can you do the same with this formula : =IF(A8<"";IF(ISNA(VLOOKUP($A8;Sheet2!$J$1:$L$20;3 ;FALSE));0;VLOOKUP($A8;Sheet2!$J$1:$L$20;3;FALSE)) ;"")) and exclude parenthesis "(ace)" like in the last formula? Thanks allot. I hope you can help me with this one too. Anyway, you really helped me and i wish you all the best, thanks. "Stefi" a scris: Maybe you need this formula: =IF(VLOOKUP(TRIM(LEFT(A2;SEARCH("(";A2)-1));planning!A:B;2;FALSE)=TEXT(B2;"dddd");"YES";"N O") It'll lookup "john ed", "john 117" in planning sheet, so you must have the names in these formats in planning sheet. I don't understand this sentence of yours: "so i don't want to extract the first word, i want to exclude the parenthesis." If you want only john, mary in the planning sheet to be found, you have to extract the first word. In this case replace TRIM(LEFT(A2;SEARCH("(";A2)-1)) by TRIM(LEFT(A2;SEARCH(" ";A2)-1)) in the formula! Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: Stefi, i have another problem. i have no answer yet, so....you are my last hope :) here are the mesages: (1.) Stefi, can you help me with one last problem? at least i hope so.... It's about date formula... =IF(VLOOKUP(A1;PLANNING!B:C;2;FALSE)=TEXT(B5;"dddd ");"YES";"NO") Example: a b c planning! b c 1 john (ace) 11.05.2008 no john monday 2 mary (mer) 16.05.2008 yes mary friday i would like formula to ignore parenthesis " (ace) ; (mer) " in my table i will write like this :john (ace) and mary (mer), but in database it will be without parenthesis : "john ; mary"... Can formula extract only john or mary? ..and ignore the rest? it is posible? THANKS ALLOT (2.) This will extract the name before (ace) =MID(A1,1,((FIND(" ",A1,1)-1))) edvwvw (3.) i want to use this formula =IF(VLOOKUP(A1;PLANNING!B:C;2;FALSE)=TEXT(B5;"dddd ");"YES";"NO") and extract everithing from cell a1 but without parenthesis. i will try to explain better in my example, and how this formula works for me: a b c PLANNING! b c 1 john 11.05.2008 no john monday 2 mary 16.05.2008 yes mary friday 3 john 12.05.2008 yes the formula tell me if john or mary arrive in the correct day. the problem is thet after john, it will be one or two more words, even numbers example : " john ed (ace)" ; "john 117 (ace)" . so i don't want to extract the first word, i want to exclude the parenthesis. and i wont to use the formula i used before. with a little modification... the new table will look like this: a b c PLANNING! b c 1 john 117 (ace) 11.05.2008 no john monday 2 mary re (mer) 16.05.2008 yes mary friday 3 john ed (ace) 12.05.2008 yes john 117 monday I need to use the same formula and to write in a1 "john 117 (ace)" but the formula to see it as john 117 and to tell me if john it's comming in the correct day. THANKS i hope you can help me. plsssssss thanks Stefi :) "Stefi" a scris: You are welcome! Thanks for the feedback! Stefi €˛puiuluipui€¯ ezt Ć*rta: THANKS ALLOT!!! You again Stefi..:) Thanks! "Stefi" a scris: =IF(A8<"";IF(ISNA(VLOOKUP($A8;Sheet2!$J$1:$L$20;3 ;FALSE));0;VLOOKUP($A8;Sheet2!$J$1:$L$20;3;FALSE)) ;"")) Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: Hi, i use this formula to extract some prices (Formula is in B cell).. (=IF(A8<"";IF(ISNA(VLOOKUP($A8;$J$1:K20;2;FALSE)) ;0;VLOOKUP($A8;$J$1:K20;2;FALSE));"")) ...but i want to extract from "J" and "L". I dont want columns to be one next to the other. My formula extract prices from "J" and "K". And i would like database ("j" and "L") to be in another sheet. This is my actual table (database in the same sheet): A B... ...J K 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 ...but i want to skip "K" and extract from column "J" and "L" like this (database to be in another sheet): A B... (another sheet) ...J K L 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 Thanks in advance. |
vlookup
Just replace A8 by TRIM(LEFT(A8;SEARCH("(";A8)-1)) in VLOOKUPs:
=IF(A8<"";IF(ISNA(VLOOKUP(TRIM(LEFT(A8;SEARCH("(" ;A8)-1));Sheet2!$J$1:$L$20;3;FALSE));0;VLOOKUP(TRIM(LEF T(A8;SEARCH("(";A8)-1));Sheet2!$J$1:$L$20;3;FALSE));"")) I didn't understand "if i put "(ace)" next to product, the formula doesn't work anymore" What is product? You haven't mentioned it up to now! Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: You are the best. it works. But now i have another problem. You transformed a formula for me and works great, but now if i put "(ace)" next to product, the formula doesn't work anymore. Can you do the same with this formula : =IF(A8<"";IF(ISNA(VLOOKUP($A8;Sheet2!$J$1:$L$20;3 ;FALSE));0;VLOOKUP($A8;Sheet2!$J$1:$L$20;3;FALSE)) ;"")) and exclude parenthesis "(ace)" like in the last formula? Thanks allot. I hope you can help me with this one too. Anyway, you really helped me and i wish you all the best, thanks. "Stefi" a scris: Maybe you need this formula: =IF(VLOOKUP(TRIM(LEFT(A2;SEARCH("(";A2)-1));planning!A:B;2;FALSE)=TEXT(B2;"dddd");"YES";"N O") It'll lookup "john ed", "john 117" in planning sheet, so you must have the names in these formats in planning sheet. I don't understand this sentence of yours: "so i don't want to extract the first word, i want to exclude the parenthesis." If you want only john, mary in the planning sheet to be found, you have to extract the first word. In this case replace TRIM(LEFT(A2;SEARCH("(";A2)-1)) by TRIM(LEFT(A2;SEARCH(" ";A2)-1)) in the formula! Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: Stefi, i have another problem. i have no answer yet, so....you are my last hope :) here are the mesages: (1.) Stefi, can you help me with one last problem? at least i hope so.... It's about date formula... =IF(VLOOKUP(A1;PLANNING!B:C;2;FALSE)=TEXT(B5;"dddd ");"YES";"NO") Example: a b c planning! b c 1 john (ace) 11.05.2008 no john monday 2 mary (mer) 16.05.2008 yes mary friday i would like formula to ignore parenthesis " (ace) ; (mer) " in my table i will write like this :john (ace) and mary (mer), but in database it will be without parenthesis : "john ; mary"... Can formula extract only john or mary? ..and ignore the rest? it is posible? THANKS ALLOT (2.) This will extract the name before (ace) =MID(A1,1,((FIND(" ",A1,1)-1))) edvwvw (3.) i want to use this formula =IF(VLOOKUP(A1;PLANNING!B:C;2;FALSE)=TEXT(B5;"dddd ");"YES";"NO") and extract everithing from cell a1 but without parenthesis. i will try to explain better in my example, and how this formula works for me: a b c PLANNING! b c 1 john 11.05.2008 no john monday 2 mary 16.05.2008 yes mary friday 3 john 12.05.2008 yes the formula tell me if john or mary arrive in the correct day. the problem is thet after john, it will be one or two more words, even numbers example : " john ed (ace)" ; "john 117 (ace)" . so i don't want to extract the first word, i want to exclude the parenthesis. and i wont to use the formula i used before. with a little modification... the new table will look like this: a b c PLANNING! b c 1 john 117 (ace) 11.05.2008 no john monday 2 mary re (mer) 16.05.2008 yes mary friday 3 john ed (ace) 12.05.2008 yes john 117 monday I need to use the same formula and to write in a1 "john 117 (ace)" but the formula to see it as john 117 and to tell me if john it's comming in the correct day. THANKS i hope you can help me. plsssssss thanks Stefi :) "Stefi" a scris: You are welcome! Thanks for the feedback! Stefi €˛puiuluipui€¯ ezt Ć*rta: THANKS ALLOT!!! You again Stefi..:) Thanks! "Stefi" a scris: =IF(A8<"";IF(ISNA(VLOOKUP($A8;Sheet2!$J$1:$L$20;3 ;FALSE));0;VLOOKUP($A8;Sheet2!$J$1:$L$20;3;FALSE)) ;"")) Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: Hi, i use this formula to extract some prices (Formula is in B cell).. (=IF(A8<"";IF(ISNA(VLOOKUP($A8;$J$1:K20;2;FALSE)) ;0;VLOOKUP($A8;$J$1:K20;2;FALSE));"")) ...but i want to extract from "J" and "L". I dont want columns to be one next to the other. My formula extract prices from "J" and "K". And i would like database ("j" and "L") to be in another sheet. This is my actual table (database in the same sheet): A B... ...J K 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 ...but i want to skip "K" and extract from column "J" and "L" like this (database to be in another sheet): A B... (another sheet) ...J K L 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 Thanks in advance. |
vlookup
ITS WORKING!
The formula was for some prices, and i used it for "john" . I use 2 formulas. One for date with "yes or no", and the other to tell me john's postal code. After you make both formula to work with database (planning), i realize that i will have put (write), (ace) next to john. "john ed (ace)" Initial was john, and formula was very good. Then was "john ed (ace)" and both formula worked no more. So i had to ignore parenthesis. And you helped me again with this new problem. And last formula worked too. Thanks allot! By the way, if in the future i will have any other problems, how can i reach you, i can write you here? i mean in "vlookup" ? P.S. Sorry, my english is so poor and i cant explain so you can easily understand. But you are great. Thanks "Stefi" a scris: Just replace A8 by TRIM(LEFT(A8;SEARCH("(";A8)-1)) in VLOOKUPs: =IF(A8<"";IF(ISNA(VLOOKUP(TRIM(LEFT(A8;SEARCH("(" ;A8)-1));Sheet2!$J$1:$L$20;3;FALSE));0;VLOOKUP(TRIM(LEF T(A8;SEARCH("(";A8)-1));Sheet2!$J$1:$L$20;3;FALSE));"")) I didn't understand "if i put "(ace)" next to product, the formula doesn't work anymore" What is product? You haven't mentioned it up to now! Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: You are the best. it works. But now i have another problem. You transformed a formula for me and works great, but now if i put "(ace)" next to product, the formula doesn't work anymore. Can you do the same with this formula : =IF(A8<"";IF(ISNA(VLOOKUP($A8;Sheet2!$J$1:$L$20;3 ;FALSE));0;VLOOKUP($A8;Sheet2!$J$1:$L$20;3;FALSE)) ;"")) and exclude parenthesis "(ace)" like in the last formula? Thanks allot. I hope you can help me with this one too. Anyway, you really helped me and i wish you all the best, thanks. "Stefi" a scris: Maybe you need this formula: =IF(VLOOKUP(TRIM(LEFT(A2;SEARCH("(";A2)-1));planning!A:B;2;FALSE)=TEXT(B2;"dddd");"YES";"N O") It'll lookup "john ed", "john 117" in planning sheet, so you must have the names in these formats in planning sheet. I don't understand this sentence of yours: "so i don't want to extract the first word, i want to exclude the parenthesis." If you want only john, mary in the planning sheet to be found, you have to extract the first word. In this case replace TRIM(LEFT(A2;SEARCH("(";A2)-1)) by TRIM(LEFT(A2;SEARCH(" ";A2)-1)) in the formula! Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: Stefi, i have another problem. i have no answer yet, so....you are my last hope :) here are the mesages: (1.) Stefi, can you help me with one last problem? at least i hope so.... It's about date formula... =IF(VLOOKUP(A1;PLANNING!B:C;2;FALSE)=TEXT(B5;"dddd ");"YES";"NO") Example: a b c planning! b c 1 john (ace) 11.05.2008 no john monday 2 mary (mer) 16.05.2008 yes mary friday i would like formula to ignore parenthesis " (ace) ; (mer) " in my table i will write like this :john (ace) and mary (mer), but in database it will be without parenthesis : "john ; mary"... Can formula extract only john or mary? ..and ignore the rest? it is posible? THANKS ALLOT (2.) This will extract the name before (ace) =MID(A1,1,((FIND(" ",A1,1)-1))) edvwvw (3.) i want to use this formula =IF(VLOOKUP(A1;PLANNING!B:C;2;FALSE)=TEXT(B5;"dddd ");"YES";"NO") and extract everithing from cell a1 but without parenthesis. i will try to explain better in my example, and how this formula works for me: a b c PLANNING! b c 1 john 11.05.2008 no john monday 2 mary 16.05.2008 yes mary friday 3 john 12.05.2008 yes the formula tell me if john or mary arrive in the correct day. the problem is thet after john, it will be one or two more words, even numbers example : " john ed (ace)" ; "john 117 (ace)" . so i don't want to extract the first word, i want to exclude the parenthesis. and i wont to use the formula i used before. with a little modification... the new table will look like this: a b c PLANNING! b c 1 john 117 (ace) 11.05.2008 no john monday 2 mary re (mer) 16.05.2008 yes mary friday 3 john ed (ace) 12.05.2008 yes john 117 monday I need to use the same formula and to write in a1 "john 117 (ace)" but the formula to see it as john 117 and to tell me if john it's comming in the correct day. THANKS i hope you can help me. plsssssss thanks Stefi :) "Stefi" a scris: You are welcome! Thanks for the feedback! Stefi €˛puiuluipui€¯ ezt Ć*rta: THANKS ALLOT!!! You again Stefi..:) Thanks! "Stefi" a scris: =IF(A8<"";IF(ISNA(VLOOKUP($A8;Sheet2!$J$1:$L$20;3 ;FALSE));0;VLOOKUP($A8;Sheet2!$J$1:$L$20;3;FALSE)) ;"")) Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: Hi, i use this formula to extract some prices (Formula is in B cell).. (=IF(A8<"";IF(ISNA(VLOOKUP($A8;$J$1:K20;2;FALSE)) ;0;VLOOKUP($A8;$J$1:K20;2;FALSE));"")) ...but i want to extract from "J" and "L". I dont want columns to be one next to the other. My formula extract prices from "J" and "K". And i would like database ("j" and "L") to be in another sheet. This is my actual table (database in the same sheet): A B... ...J K 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 ...but i want to skip "K" and extract from column "J" and "L" like this (database to be in another sheet): A B... (another sheet) ...J K L 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 Thanks in advance. |
vlookup
You are welcome! Thanks for the feedback! Just post to this forum, you'll get
help, either from me or others much better than me! Stefi €˛puiuluipui€¯ ezt Ć*rta: ITS WORKING! The formula was for some prices, and i used it for "john" . I use 2 formulas. One for date with "yes or no", and the other to tell me john's postal code. After you make both formula to work with database (planning), i realize that i will have put (write), (ace) next to john. "john ed (ace)" Initial was john, and formula was very good. Then was "john ed (ace)" and both formula worked no more. So i had to ignore parenthesis. And you helped me again with this new problem. And last formula worked too. Thanks allot! By the way, if in the future i will have any other problems, how can i reach you, i can write you here? i mean in "vlookup" ? P.S. Sorry, my english is so poor and i cant explain so you can easily understand. But you are great. Thanks "Stefi" a scris: Just replace A8 by TRIM(LEFT(A8;SEARCH("(";A8)-1)) in VLOOKUPs: =IF(A8<"";IF(ISNA(VLOOKUP(TRIM(LEFT(A8;SEARCH("(" ;A8)-1));Sheet2!$J$1:$L$20;3;FALSE));0;VLOOKUP(TRIM(LEF T(A8;SEARCH("(";A8)-1));Sheet2!$J$1:$L$20;3;FALSE));"")) I didn't understand "if i put "(ace)" next to product, the formula doesn't work anymore" What is product? You haven't mentioned it up to now! Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: You are the best. it works. But now i have another problem. You transformed a formula for me and works great, but now if i put "(ace)" next to product, the formula doesn't work anymore. Can you do the same with this formula : =IF(A8<"";IF(ISNA(VLOOKUP($A8;Sheet2!$J$1:$L$20;3 ;FALSE));0;VLOOKUP($A8;Sheet2!$J$1:$L$20;3;FALSE)) ;"")) and exclude parenthesis "(ace)" like in the last formula? Thanks allot. I hope you can help me with this one too. Anyway, you really helped me and i wish you all the best, thanks. "Stefi" a scris: Maybe you need this formula: =IF(VLOOKUP(TRIM(LEFT(A2;SEARCH("(";A2)-1));planning!A:B;2;FALSE)=TEXT(B2;"dddd");"YES";"N O") It'll lookup "john ed", "john 117" in planning sheet, so you must have the names in these formats in planning sheet. I don't understand this sentence of yours: "so i don't want to extract the first word, i want to exclude the parenthesis." If you want only john, mary in the planning sheet to be found, you have to extract the first word. In this case replace TRIM(LEFT(A2;SEARCH("(";A2)-1)) by TRIM(LEFT(A2;SEARCH(" ";A2)-1)) in the formula! Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: Stefi, i have another problem. i have no answer yet, so....you are my last hope :) here are the mesages: (1.) Stefi, can you help me with one last problem? at least i hope so.... It's about date formula... =IF(VLOOKUP(A1;PLANNING!B:C;2;FALSE)=TEXT(B5;"dddd ");"YES";"NO") Example: a b c planning! b c 1 john (ace) 11.05.2008 no john monday 2 mary (mer) 16.05.2008 yes mary friday i would like formula to ignore parenthesis " (ace) ; (mer) " in my table i will write like this :john (ace) and mary (mer), but in database it will be without parenthesis : "john ; mary"... Can formula extract only john or mary? ..and ignore the rest? it is posible? THANKS ALLOT (2.) This will extract the name before (ace) =MID(A1,1,((FIND(" ",A1,1)-1))) edvwvw (3.) i want to use this formula =IF(VLOOKUP(A1;PLANNING!B:C;2;FALSE)=TEXT(B5;"dddd ");"YES";"NO") and extract everithing from cell a1 but without parenthesis. i will try to explain better in my example, and how this formula works for me: a b c PLANNING! b c 1 john 11.05.2008 no john monday 2 mary 16.05.2008 yes mary friday 3 john 12.05.2008 yes the formula tell me if john or mary arrive in the correct day. the problem is thet after john, it will be one or two more words, even numbers example : " john ed (ace)" ; "john 117 (ace)" . so i don't want to extract the first word, i want to exclude the parenthesis. and i wont to use the formula i used before. with a little modification... the new table will look like this: a b c PLANNING! b c 1 john 117 (ace) 11.05.2008 no john monday 2 mary re (mer) 16.05.2008 yes mary friday 3 john ed (ace) 12.05.2008 yes john 117 monday I need to use the same formula and to write in a1 "john 117 (ace)" but the formula to see it as john 117 and to tell me if john it's comming in the correct day. THANKS i hope you can help me. plsssssss thanks Stefi :) "Stefi" a scris: You are welcome! Thanks for the feedback! Stefi €˛puiuluipui€¯ ezt Ć*rta: THANKS ALLOT!!! You again Stefi..:) Thanks! "Stefi" a scris: =IF(A8<"";IF(ISNA(VLOOKUP($A8;Sheet2!$J$1:$L$20;3 ;FALSE));0;VLOOKUP($A8;Sheet2!$J$1:$L$20;3;FALSE)) ;"")) Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: Hi, i use this formula to extract some prices (Formula is in B cell).. (=IF(A8<"";IF(ISNA(VLOOKUP($A8;$J$1:K20;2;FALSE)) ;0;VLOOKUP($A8;$J$1:K20;2;FALSE));"")) ...but i want to extract from "J" and "L". I dont want columns to be one next to the other. My formula extract prices from "J" and "K". And i would like database ("j" and "L") to be in another sheet. This is my actual table (database in the same sheet): A B... ...J K 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 ...but i want to skip "K" and extract from column "J" and "L" like this (database to be in another sheet): A B... (another sheet) ...J K L 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 Thanks in advance. |
vlookup
Stefi, both formulas are ignoring parenthesis, but when i write something
without parenthesis, it showing "#value!" formula 1 =IF(VLOOKUP(TRIM(LEFT(A2;SEARCH("(";A2)-1));planning!A:B;2;FALSE)=TEXT(B2;"dddd");"YES";"N O") formula 2 =IF(A1<"";IF(ISNA(VLOOKUP(TRIM(LEFT(A1;SEARCH("(" ;A1)-1));planning!$A$1:$C$20;3;FALSE));0;VLOOKUP(TRIM(L EFT(A1;SEARCH("(";A1)-1));planning!$A$1:$C$20;3;FALSE));"")) my final table it's looking like this: A B C D PLANNING! A B C 1 john ed 16.05.2008 formula 1 formula 2 john monday id 05 2 john 12.05.2008 formula 1 formula 2 john ed monday id 06 3 john ed (ace) 11.05.2008 formula 1 formula 2 john 117 monday id 08 4 john 117 (ace) 06.05.2008 formula 1 formula 2 5 etc.. with formula...how i want to be: A B C D PLANNING! A B C 1 john ed 16.05.2008 no id 06 john monday id 05 2 john 12.05.2008 yes id 05 john ed monday id 06 3 john ed (ace) 11.05.2008 no id 06 john 117 monday id 08 4 john 117 (ace) 06.05.2008 no id 08 5 etc.. but it's looking like this: A B C D PLANNING! A B C 1 john ed 16.05.2008 #value! #value! john monday id 05 2 john 12.05.2008 #value! #value! john ed monday id 06 3 john ed (ace) 11.05.2008 no id 06 john 117 monday id 08 4 john 117 (ace) 06.05.2008 no id 08 5 etc.. if there is no parenthesis, it's showing #value! with parenthesis it's showing what i need i want the formula to work with and without parenthesis "(ace)" example: john ed (ace) no id 06 john ed no id 06 thanks "Stefi" a scris: You are welcome! Thanks for the feedback! Just post to this forum, you'll get help, either from me or others much better than me! Stefi €˛puiuluipui€¯ ezt Ć*rta: ITS WORKING! The formula was for some prices, and i used it for "john" . I use 2 formulas. One for date with "yes or no", and the other to tell me john's postal code. After you make both formula to work with database (planning), i realize that i will have put (write), (ace) next to john. "john ed (ace)" Initial was john, and formula was very good. Then was "john ed (ace)" and both formula worked no more. So i had to ignore parenthesis. And you helped me again with this new problem. And last formula worked too. Thanks allot! By the way, if in the future i will have any other problems, how can i reach you, i can write you here? i mean in "vlookup" ? P.S. Sorry, my english is so poor and i cant explain so you can easily understand. But you are great. Thanks "Stefi" a scris: Just replace A8 by TRIM(LEFT(A8;SEARCH("(";A8)-1)) in VLOOKUPs: =IF(A8<"";IF(ISNA(VLOOKUP(TRIM(LEFT(A8;SEARCH("(" ;A8)-1));Sheet2!$J$1:$L$20;3;FALSE));0;VLOOKUP(TRIM(LEF T(A8;SEARCH("(";A8)-1));Sheet2!$J$1:$L$20;3;FALSE));"")) I didn't understand "if i put "(ace)" next to product, the formula doesn't work anymore" What is product? You haven't mentioned it up to now! Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: You are the best. it works. But now i have another problem. You transformed a formula for me and works great, but now if i put "(ace)" next to product, the formula doesn't work anymore. Can you do the same with this formula : =IF(A8<"";IF(ISNA(VLOOKUP($A8;Sheet2!$J$1:$L$20;3 ;FALSE));0;VLOOKUP($A8;Sheet2!$J$1:$L$20;3;FALSE)) ;"")) and exclude parenthesis "(ace)" like in the last formula? Thanks allot. I hope you can help me with this one too. Anyway, you really helped me and i wish you all the best, thanks. "Stefi" a scris: Maybe you need this formula: =IF(VLOOKUP(TRIM(LEFT(A2;SEARCH("(";A2)-1));planning!A:B;2;FALSE)=TEXT(B2;"dddd");"YES";"N O") It'll lookup "john ed", "john 117" in planning sheet, so you must have the names in these formats in planning sheet. I don't understand this sentence of yours: "so i don't want to extract the first word, i want to exclude the parenthesis." If you want only john, mary in the planning sheet to be found, you have to extract the first word. In this case replace TRIM(LEFT(A2;SEARCH("(";A2)-1)) by TRIM(LEFT(A2;SEARCH(" ";A2)-1)) in the formula! Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: Stefi, i have another problem. i have no answer yet, so....you are my last hope :) here are the mesages: (1.) Stefi, can you help me with one last problem? at least i hope so.... It's about date formula... =IF(VLOOKUP(A1;PLANNING!B:C;2;FALSE)=TEXT(B5;"dddd ");"YES";"NO") Example: a b c planning! b c 1 john (ace) 11.05.2008 no john monday 2 mary (mer) 16.05.2008 yes mary friday i would like formula to ignore parenthesis " (ace) ; (mer) " in my table i will write like this :john (ace) and mary (mer), but in database it will be without parenthesis : "john ; mary"... Can formula extract only john or mary? ..and ignore the rest? it is posible? THANKS ALLOT (2.) This will extract the name before (ace) =MID(A1,1,((FIND(" ",A1,1)-1))) edvwvw (3.) i want to use this formula =IF(VLOOKUP(A1;PLANNING!B:C;2;FALSE)=TEXT(B5;"dddd ");"YES";"NO") and extract everithing from cell a1 but without parenthesis. i will try to explain better in my example, and how this formula works for me: a b c PLANNING! b c 1 john 11.05.2008 no john monday 2 mary 16.05.2008 yes mary friday 3 john 12.05.2008 yes the formula tell me if john or mary arrive in the correct day. the problem is thet after john, it will be one or two more words, even numbers example : " john ed (ace)" ; "john 117 (ace)" . so i don't want to extract the first word, i want to exclude the parenthesis. and i wont to use the formula i used before. with a little modification... the new table will look like this: a b c PLANNING! b c 1 john 117 (ace) 11.05.2008 no john monday 2 mary re (mer) 16.05.2008 yes mary friday 3 john ed (ace) 12.05.2008 yes john 117 monday I need to use the same formula and to write in a1 "john 117 (ace)" but the formula to see it as john 117 and to tell me if john it's comming in the correct day. THANKS i hope you can help me. plsssssss thanks Stefi :) "Stefi" a scris: You are welcome! Thanks for the feedback! Stefi €˛puiuluipui€¯ ezt Ć*rta: THANKS ALLOT!!! You again Stefi..:) Thanks! "Stefi" a scris: =IF(A8<"";IF(ISNA(VLOOKUP($A8;Sheet2!$J$1:$L$20;3 ;FALSE));0;VLOOKUP($A8;Sheet2!$J$1:$L$20;3;FALSE)) ;"")) Regards, Stefi €˛puiuluipui€¯ ezt Ć*rta: Hi, i use this formula to extract some prices (Formula is in B cell).. (=IF(A8<"";IF(ISNA(VLOOKUP($A8;$J$1:K20;2;FALSE)) ;0;VLOOKUP($A8;$J$1:K20;2;FALSE));"")) ...but i want to extract from "J" and "L". I dont want columns to be one next to the other. My formula extract prices from "J" and "K". And i would like database ("j" and "L") to be in another sheet. This is my actual table (database in the same sheet): A B... ...J K 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 ...but i want to skip "K" and extract from column "J" and "L" like this (database to be in another sheet): A B... (another sheet) ...J K L 1 product 1 100 product 1 100 2 product 3 120 product 2 170 3 product 2 170 product 3 120 Thanks in advance. |
vlookup
formula 1 =IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEF T(A2,SEARCH("(",A2)-1))),planning!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES"," NO") formula 2 =IF(A2<"",IF(ISNA(VLOOKUP( IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEFT(A2,SEARCH( "(",A2)-1))),planning!$A$2:$C$20,3,FALSE)),0, VLOOKUP( IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEFT(A2,SEARCH( "(",A2)-1))),planning!$A$2:$C$20,3,FALSE)),"") Regards, Stefi |
vlookup
formula 1 =IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEF T(A2,SEARCH("(",A2)-1))),planning!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES"," NO") formula 2 =IF(A2<"",IF(ISNA(VLOOKUP( IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEFT(A2,SEARCH( "(",A2)-1))),planning!$A$2:$C$20,3,FALSE)),0, VLOOKUP( IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEFT(A2,SEARCH( "(",A2)-1))),planning!$A$2:$C$20,3,FALSE)),"") Regards, Stefi |
vlookup
It's working!
Thanks allot Stefi! You are great! Thanks "Stefi" a scris: formula 1 =IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEF T(A2,SEARCH("(",A2)-1))),planning!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES"," NO") formula 2 =IF(A2<"",IF(ISNA(VLOOKUP( IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEFT(A2,SEARCH( "(",A2)-1))),planning!$A$2:$C$20,3,FALSE)),0, VLOOKUP( IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEFT(A2,SEARCH( "(",A2)-1))),planning!$A$2:$C$20,3,FALSE)),"") Regards, Stefi |
vlookup
Stefi, dont hate me, but i need 2 more things:
this is the most important.. if i don't write nothing (john) the formula 1 it's showing #N/A i dont want formula to show me anything...until i write something in cell A i mean the formula to work after i write "john" and, if i write wrong "jonn" instead of "john", the formula to show me no planning. the formula to have "yes", "no", and "no planning". (Like in the second formula) formula 1 =IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEF T(A2,SEARCH("(",A2)-1))),PLANNING!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES"," NO") and if there is a possibility "no planning" to appear written in red??? and if it is possible, i will need the same thing for this formula too: formula 2 =IF(A8<"";IF(ISNA(VLOOKUP($A8;Sheet2!$J$1:$L$20;3 ;FALSE));no planning;VLOOKUP($A8;Sheet2!$J$1:$L$20;3;FALSE));" ")) thanks. and...thanks! "puiuluipui" a scris: It's working! Thanks allot Stefi! You are great! Thanks "Stefi" a scris: formula 1 =IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEF T(A2,SEARCH("(",A2)-1))),planning!A:B,2,FALSE)=TEXT(B2,"nnnn"),"YES"," NO") formula 2 =IF(A2<"",IF(ISNA(VLOOKUP( IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEFT(A2,SEARCH( "(",A2)-1))),planning!$A$2:$C$20,3,FALSE)),0, VLOOKUP( IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEFT(A2,SEARCH( "(",A2)-1))),planning!$A$2:$C$20,3,FALSE)),"") Regards, Stefi |
| All times are GMT +1. The time now is 09:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com