Ask Your Question

# compare prices and add comment

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 close merge delete

## 2 Answers

Sort by » oldest newest most voted

In B1: =IFNA(VLOOKUP($A1;$C$1:$D$70;2;0);"") The 70 here could change when the C and D data count changes 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

( 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

( 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

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

Here are the help topics for these functions:

( 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)))

( 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.

( 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)),"") ( 2017-09-15 11:00:47 +0200 )edit 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!!!

more

## Stats

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

Seen: 170 times

Last updated: Sep 11 '17