| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
| IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Hybrid Mode
