Ask Your Question
0

compare prices and add comment

asked 2017-09-06 14:47:10 +0200

dimzev gravatar image

updated 2017-09-11 22:10:35 +0200

I have 3 columns A, B,C , D

Α has 3000 cells

B empty cells

C and D have 70 cells

i want : if some cells from A = some cells from B write to column B the corresponding C

for example :

A153 = C27 then B153=D27 A1832 = C38 then B1832=D38

A857 not = with none from column C then write B857= empty cell

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2017-09-06 14:51:09 +0200

updated 2017-09-07 16:52:42 +0200

In B1: =IFNA(VLOOKUP($A1;$C$1:$D$70;2;0);"")

The 70 here could change when the C and D data count changes

edit flag offensive delete link more

Comments

my friend , the code : =IF(ISNA(VLOOKUP($A10;$C$1:$D$4;2;0));"";VLOOKUP($A10;$C$1:$D$4;2;0)) don't give me the right answer but the code : =VLOOKUP($A1;$C$1:$D$70;2;0) give me right answer but the problem is in the column B for empty cells give me #N/A can you fix this please ? thank you

dimzev gravatar imagedimzev ( 2017-09-06 17:31:42 +0200 )edit

Α: 65 cells B: empty cells c: 12 cells D: 12 cells When use your command take wrong answers on B column. can you see again ? and tell me what and where changes have make for different numbers of (A, and B,C) columns Thank you for your try

dimzev gravatar imagedimzev ( 2017-09-07 16:01:54 +0200 )edit

i try use VLOOKUP with 2 or more sheets but not succeed...

i try something like that

=IF(VLOOKUP($A2,$'Α φάση παράλληλη'.$J$2:$K$34,2,0),VLOOKUP($A2,$'Α φάση ΣΜΕΑΕ'.$K$2:$L$134,2,0),"")

or

=IFNA(VLOOKUP($A2,$'Α φάση παράλληλη'.$J$2:$K$34,2,0),VLOOKUP($A2,$'Α φάση ΣΜΕΑΕ'.$K$2:$L$134,2,0),"")

and take #N/A or error504

can help me which is the correct code please

dimzev gravatar imagedimzev ( 2017-09-14 17:20:58 +0200 )edit

Here are the help topics for these functions:

Mike Kaganski gravatar imageMike Kaganski ( 2017-09-14 22:04:57 +0200 )edit

The first function =IF(VLOOKUP($A2,$'Α φάση παράλληλη'.$J$2:$K$34,2,0),VLOOKUP($A2,$'Α φάση ΣΜΕΑΕ'.$K$2:$L$134,2,0),"") is very different from the variant I gave initially. It should first check if the result of the VLOOKUP is NA, and in this case return empty string, otherwise repeat the VLOOKUP. Its weakness is that you need to call the same VLOOKUP twice.

=IF(ISNA(VLOOKUP($A2,$'Α φάση παράλληλη'.$J$2:$K$34,2,0),"",VLOOKUP($A2,$'Α φάση παράλληλη'.$J$2:$K$34,2,0)))

Mike Kaganski gravatar imageMike Kaganski ( 2017-09-14 22:08:28 +0200 )edit

The second function does not have the said drawback. But you managed to write it wrong, too. It should read

=IFNA(VLOOKUP($A2,$'Α φάση παράλληλη'.$J$2:$K$34,2,0),"")

In both cases, all you need is to adjust ranges in all VLOOKUPs uniformly.

Mike Kaganski gravatar imageMike Kaganski ( 2017-09-14 22:11:03 +0200 )edit

thank you my friend for your help!! i post the answer in new question ... example for 2 tables:

=IFNA(IF($A1="",VLOOKUP($A1,$'Sheet1'.$J$1:$K$34,2,0),VLOOKUP($A1,$'Sheet2'.$K$1:$L$134,2,0)),"")

dimzev gravatar imagedimzev ( 2017-09-15 11:00:47 +0200 )edit
0

answered 2017-09-07 16:59:03 +0200

dimzev gravatar image

This is !!!! =IFNA(VLOOKUP($A1;$C$1:$D$70;2;0);"") or =IFNA(VLOOKUP($A1;$C$1:$D$12;2;0);"")

Thank you very much my friend!!!

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-09-06 14:47:10 +0200

Seen: 150 times

Last updated: Sep 11 '17