vlookup

[puiuluipui]

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.

 

[Stefi]

=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:

 

[puiuluipui]

THANKS ALLOT!!!
You again Stefi..
Thanks!

"Stefi" a scris:

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„puiuluipui†ezt írta:

 

[puiuluipui]

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:

 

[Stefi]

Maybe you need this formula:
=IF(VLOOKUP(TRIM(LEFT(A2;SEARCH("(";A2)-1));planning!A:B;2;FALSE)=TEXT(B2;"dddd");"YES";"NO")

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:

 

[puiuluipui]

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:

 

[Stefi]

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(LEFT(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:

 

[puiuluipui]

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:

 

[Stefi]

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:

 

[puiuluipui]

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";"NO")

formula 2

=IF(A1<>"";IF(ISNA(VLOOKUP(TRIM(LEFT(A1;SEARCH("(";A1)-1));planning!$A$1:$C$20;3;FALSE));0;VLOOKUP(TRIM(LEFT(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:

 

[Stefi]

formula 1 =IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEFT(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

 

[Stefi]

formula 1 =IF(VLOOKUP(IF(ISERROR(SEARCH("(",A2)),A2,TRIM(LEFT(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

 

[puiuluipui]

It's working!
Thanks allot Stefi!
You are great! Thanks








"Stefi" a scris:

 

[puiuluipui]

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(LEFT(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: