Ask Your Question
0

VLOOKUP on Scalc [closed]

asked 2012-12-20 09:26:39 +0200

Fayaz gravatar image

When I use the following function "=VLOOKUP(A8,Sheet2.A3:E71,3,0)"

If the statement is correct, it displays the corresponding data. But if the data is not there, #N/A is being displayed. What I need to know is, instead of "#N/A" can "0" be displayed? I cannot upload spreadsheet as I am new to this site. So I have given the contents below

      SHEET 1

SKU Item Qty CSAWXXEG001 test1 10 CSAWXXSA001 test2 15 CSWWMRDK001 test3 29 CSWWMRDK003 test4 45 CSWWSOHU001 test5 67 CSHLCRCY001 test6 #N/A CSHWCRCY001 test7 82 CSHWCRCY002 test8 91 CSKWGHHU001 test9 #N/A

      SHEET 2

ITEM NO QTY WGT Price CSAWXXEG001 test1 10 4,680.00 CSAWXXSA001 test2 15 25.00 CSWWMRDK001 test3 29 600 CSWWMRDK003 test4 45 2,250.00 CSWWSOHU001 test5 67 500 CSHWCRCY001 test6 82 6,500.00 CSHWCRCY002 test7 91 100 VGPSLZEG001 test8 14 3,530.00 VGPSMOEG001 test9 31 720 VGRCLWNL001 test10 99 50 VGRCLWNL002 test11 35 60 CSWWMRDK003 test12 87 2,250.00

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-19 14:04:09.348853

1 Answer

Sort by » oldest newest most voted
0

answered 2012-12-20 10:06:40 +0200

JohnSUN gravatar image

Just validate result of VLOOKUP with function ISERROR() and get result with function IF()

=IF(ISERROR(VLOOKUP(A8,Sheet2.A3:E71,3,0)),0,VLOOKUP(A8,Sheet2.A3:E71,3,0))

You can get zero (0) or "empty string" ("") as you want

edit flag offensive delete link more

Question Tools

Stats

Asked: 2012-12-20 09:26:39 +0200

Seen: 370 times

Last updated: Dec 20 '12