Vlookup function

[taych]

Hi,

Can anyone help me to convert error value of #N/A to 0 (zero) ?

A B C D
1 100 Green 100W 10
2 100 Yellow 100X 20
3 100 Blue 100Y 30
4 100 Red 100Z 40

Formula =vlookup(A1&"Y",C14,2,False) returns 30

But =vlookup(A1&"M",C14,2,False) returns error value #N/A as "100M
(A1&"M") is not available in the formula.

Is there possible to convert the formula so that the value of #N/A i
returned as 0 ?

Thanks

 

[JE McGimpsey]

One way:


=IF(ISNA(MATCH(A1&"M",C:C,FALSE)),0,VLOOKUP(A1&"M",C,2,FALSE))

 

[Dave Hawley]

Hi

Details on this and more here
http://www.ozgrid.com/Excel/excel-vlookup-formula.htm

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****