aivijardin_09
New Member
- Joined
- Mar 26, 2010
- Messages
- 37
Hi,
i am using these formula below;
=IF(ISERROR(VLOOKUP(A2,Sheet2!A:F,2,FALSE)),
VLOOKUP(B2,Sheet2!A:F,2,FALSE),VLOOKUP(A2,Sheet2!A:F,2,FALSE))
sheet 1
<table style="border-collapse: collapse; width: 375pt;" border="0" cellpadding="0" cellspacing="0" width="500"><col style="width: 48pt;" width="64"> <col style="width: 87pt;" width="116"> <col style="width: 48pt;" width="64" span="5"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; width: 48pt;" width="64" height="20">Item</td> <td class="xl64" style="border-left: medium none; width: 87pt;" width="116">Alternative Items</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Jun-09</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Jul-09</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Aug-09</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Sep-09</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Oct-09</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">12345</td> <td class="xl63" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">12398</td> <td class="xl63" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">12455</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">45678</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">004A166</td> <td class="xl63" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">004A167</td> <td class="xl63" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">00510R0810</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">510R081</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">00675R0140</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">675R014</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> </tbody></table>
sheet 2
<table style="border-collapse: collapse; width: 299pt;" border="0" cellpadding="0" cellspacing="0" width="398"><col style="width: 59pt;" width="78"> <col style="width: 48pt;" width="64" span="5"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 59pt;" width="78" height="20">Item</td> <td class="xl67" style="border-left: medium none; width: 48pt;" width="64">Jun-09</td> <td class="xl67" style="border-left: medium none; width: 48pt;" width="64">Jul-09</td> <td class="xl67" style="border-left: medium none; width: 48pt;" width="64">Aug-09</td> <td class="xl67" style="border-left: medium none; width: 48pt;" width="64">Sep-09</td> <td class="xl67" style="border-left: medium none; width: 48pt;" width="64">Oct-09</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">12345</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">12378</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">45678</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">004A166</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">004A167</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">510R081</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">675R014</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> </tbody></table>
how can i remove the #N/A without using Replace?
Thanks
i am using these formula below;
=IF(ISERROR(VLOOKUP(A2,Sheet2!A:F,2,FALSE)),
VLOOKUP(B2,Sheet2!A:F,2,FALSE),VLOOKUP(A2,Sheet2!A:F,2,FALSE))
sheet 1
<table style="border-collapse: collapse; width: 375pt;" border="0" cellpadding="0" cellspacing="0" width="500"><col style="width: 48pt;" width="64"> <col style="width: 87pt;" width="116"> <col style="width: 48pt;" width="64" span="5"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; width: 48pt;" width="64" height="20">Item</td> <td class="xl64" style="border-left: medium none; width: 87pt;" width="116">Alternative Items</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Jun-09</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Jul-09</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Aug-09</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Sep-09</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Oct-09</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">12345</td> <td class="xl63" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">12398</td> <td class="xl63" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">12455</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">45678</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">004A166</td> <td class="xl63" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">004A167</td> <td class="xl63" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">00510R0810</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">510R081</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">00675R0140</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">675R014</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> </tbody></table>
sheet 2
<table style="border-collapse: collapse; width: 299pt;" border="0" cellpadding="0" cellspacing="0" width="398"><col style="width: 59pt;" width="78"> <col style="width: 48pt;" width="64" span="5"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 59pt;" width="78" height="20">Item</td> <td class="xl67" style="border-left: medium none; width: 48pt;" width="64">Jun-09</td> <td class="xl67" style="border-left: medium none; width: 48pt;" width="64">Jul-09</td> <td class="xl67" style="border-left: medium none; width: 48pt;" width="64">Aug-09</td> <td class="xl67" style="border-left: medium none; width: 48pt;" width="64">Sep-09</td> <td class="xl67" style="border-left: medium none; width: 48pt;" width="64">Oct-09</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">12345</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">12378</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">45678</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">004A166</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">004A167</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="center">#N/A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">510R081</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">675R014</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> </tbody></table>
how can i remove the #N/A without using Replace?
Thanks