| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide #N/As with Cell Indexing/Matching
Iwas wondering if anyone knows how I can get the "#N/A" to stop being
displayed for line items that are not found? The first formula is the one I originally used and the second formula was my attempt at having it display a "No" instead of "#N/A". The second formula fails... It shows the "Yes" Just Fine but when it comes to the no part is flops and shows #N/A. INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1) =IF(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)= "#N/A","No","Yes") Thanks in Advance, Rob |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hide #N/As with Cell Indexing/Matching
tried:
=IF(ISERROR(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$15 8,0),1)),"No","Yes") ? On 13 Sty, 12:11, Rob wrote: Iwas wondering if anyone knows how I can get the "#N/A" to stop being displayed for line items that are not found? *The first formula is the one I originally used and the second formula was my attempt at having it display a "No" instead of "#N/A". *The second formula fails... *It shows the "Yes" Just Fine but when it comes to the no part is flops and shows #N/A. INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1) =IF(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)= "#N/A","No","Yes") Thanks in Advance, Rob |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hide #N/As with Cell Indexing/Matching
Rob,
Maybe this =IF(ISNA(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0 ),1)),"No","Yes") or even this =IF(ISNA(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0 ),1)),"No",INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158 ,0),1)) Mike "Rob" wrote: Iwas wondering if anyone knows how I can get the "#N/A" to stop being displayed for line items that are not found? The first formula is the one I originally used and the second formula was my attempt at having it display a "No" instead of "#N/A". The second formula fails... It shows the "Yes" Just Fine but when it comes to the no part is flops and shows #N/A. INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1) =IF(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)= "#N/A","No","Yes") Thanks in Advance, Rob |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hide #N/As with Cell Indexing/Matching
You can use ISNA to trap just the #N/A errors:
=IF(ISNA(MATCH($E6,$K$6:$K$158,0)),"no",INDEX($K$6 :$K$158,MATCH($E6,$K $6:$K$158,0),1)) Hope this helps. Pete On Jan 13, 11:11*am, Rob wrote: Iwas wondering if anyone knows how I can get the "#N/A" to stop being displayed for line items that are not found? *The first formula is the one I originally used and the second formula was my attempt at having it display a "No" instead of "#N/A". *The second formula fails... *It shows the "Yes" Just Fine but when it comes to the no part is flops and shows #N/A. INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1) =IF(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)= "#N/A","No","Yes") Thanks in Advance, Rob |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hide #N/As with Cell Indexing/Matching
WOW !! That's Awesome.. They all worked! Thanks You Very Much.
Rob "Pete_UK" wrote: You can use ISNA to trap just the #N/A errors: =IF(ISNA(MATCH($E6,$K$6:$K$158,0)),"no",INDEX($K$6 :$K$158,MATCH($E6,$K $6:$K$158,0),1)) Hope this helps. Pete On Jan 13, 11:11 am, Rob wrote: Iwas wondering if anyone knows how I can get the "#N/A" to stop being displayed for line items that are not found? The first formula is the one I originally used and the second formula was my attempt at having it display a "No" instead of "#N/A". The second formula fails... It shows the "Yes" Just Fine but when it comes to the no part is flops and shows #N/A. INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1) =IF(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)= "#N/A","No","Yes") Thanks in Advance, Rob |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hide #N/As with Cell Indexing/Matching
You're welcome, Rob - thanks for feeding back.
Pete On Jan 13, 12:14*pm, Rob wrote: WOW !! That's Awesome.. They all worked! *Thanks You Very Much. * Rob "Pete_UK" wrote: You can use ISNA to trap just the #N/A errors: =IF(ISNA(MATCH($E6,$K$6:$K$158,0)),"no",INDEX($K$6 :$K$158,MATCH($E6,$K $6:$K$158,0),1)) Hope this helps. Pete On Jan 13, 11:11 am, Rob wrote: Iwas wondering if anyone knows how I can get the "#N/A" to stop being displayed for line items that are not found? *The first formula is the one I originally used and the second formula was my attempt at having it display a "No" instead of "#N/A". *The second formula fails... *It shows the "Yes" Just Fine but when it comes to the no part is flops and shows #N/A. INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1) =IF(INDEX($K$6:$K$158,MATCH($E6,$K$6:$K$158,0),1)= "#N/A","No","Yes") Thanks in Advance, Rob- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Indexing of Name | Excel Worksheet Functions | |||
| Indexing/Matching True/False results | Excel Worksheet Functions | |||
| Matching data and linking it to the matching cell | Links and Linking in Excel | |||
| Indexing / Cell Addressing | Excel Discussion (Misc queries) | |||
| Indexing, matching and vlookups? | Excel Worksheet Functions | |||
Linear Mode
