| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I put in 19 the formula is ok but if i put in a566 it returns #N/A
Hello from Steved
Below I have 2 formulas the first works the second does not Ok my objective is that on A566 I have E19 and on All Bus Models I have 19, What do I need to do please if I put in A566 it will pick up the value from All Bus Models. Thankyou. =IF(ISNA(VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)),VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE),VLOOKUP(19,'All Bus Models'!$A$5:$C$5000,2,FALSE)) =IF(ISNA(VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)),VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE),VLOOKUP(A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I put in 19 the formula is ok but if i put in a566 it returns #N/A
I am not sure of what the question is, but in both formulae you first check
if a value is #N/A, and if it is, you use the same formula (that will show #N/A) as the result. You may try a formula like: =IF(ISNA(VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)),"Value not found",VLOOKUP((--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)) With this, it will show a message if the VLOOKUP is not successful, and return the value if it is. Hope this helps, Miguel. "Steved" wrote: Hello from Steved Below I have 2 formulas the first works the second does not Ok my objective is that on A566 I have E19 and on All Bus Models I have 19, What do I need to do please if I put in A566 it will pick up the value from All Bus Models. Thankyou. =IF(ISNA(VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)),VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE),VLOOKUP(19,'All Bus Models'!$A$5:$C$5000,2,FALSE)) =IF(ISNA(VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)),VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE),VLOOKUP(A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I put in 19 the formula is ok but if i put in a566 it returns
Hello Miguel
I see what you mean simple put i have a product number called 19 or E19 meaning one City has labelled it 19 the other city has labelled it E19 so I am wanting an eiher or formula which I have, if I put in 19 in the formula it works as it should but if I put in A Cell reference it won't, I just need to develop the formula so it will pick the cell in this case A566. Cheers. "Miguel Zapico" wrote: I am not sure of what the question is, but in both formulae you first check if a value is #N/A, and if it is, you use the same formula (that will show #N/A) as the result. You may try a formula like: =IF(ISNA(VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)),"Value not found",VLOOKUP((--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)) With this, it will show a message if the VLOOKUP is not successful, and return the value if it is. Hope this helps, Miguel. "Steved" wrote: Hello from Steved Below I have 2 formulas the first works the second does not Ok my objective is that on A566 I have E19 and on All Bus Models I have 19, What do I need to do please if I put in A566 it will pick up the value from All Bus Models. Thankyou. =IF(ISNA(VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)),VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE),VLOOKUP(19,'All Bus Models'!$A$5:$C$5000,2,FALSE)) =IF(ISNA(VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)),VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE),VLOOKUP(A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I put in 19 the formula is ok but if i put in a566 it returns
Does this double coding happens normally? If so, I would recommend creating
a new list, where you relate all the different codes that may appear on the data with the codes that are currently part of the 'All Bus Models'!$A$5:$C$5000. For example, if the value 19 is the one that works, you can create a list like this (let's say in worksheet "Aux") Column A Column B E19 19 19 19 .... (10000 rows, for example) And then change the formula to =VLOOKUP(VLOOKUP(--$A566,Aux!$A$1:$B10000,2,FALSE),'All Bus Models'!$A$5:$C$5000,2,FALSE) Would this work in your case? You can even try a mixed approach with: =IF(ISNA(VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)),VLOOKUP(VLOOKUP(--$A566,Aux!$A$1:$B500,2,FALSE),'All Bus Models'!$A$5:$C$5000,2,FALSE),VLOOKUP((--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)) Miguel. Miguel "Steved" wrote: Hello Miguel I see what you mean simple put i have a product number called 19 or E19 meaning one City has labelled it 19 the other city has labelled it E19 so I am wanting an eiher or formula which I have, if I put in 19 in the formula it works as it should but if I put in A Cell reference it won't, I just need to develop the formula so it will pick the cell in this case A566. Cheers. "Miguel Zapico" wrote: I am not sure of what the question is, but in both formulae you first check if a value is #N/A, and if it is, you use the same formula (that will show #N/A) as the result. You may try a formula like: =IF(ISNA(VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)),"Value not found",VLOOKUP((--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)) With this, it will show a message if the VLOOKUP is not successful, and return the value if it is. Hope this helps, Miguel. "Steved" wrote: Hello from Steved Below I have 2 formulas the first works the second does not Ok my objective is that on A566 I have E19 and on All Bus Models I have 19, What do I need to do please if I put in A566 it will pick up the value from All Bus Models. Thankyou. =IF(ISNA(VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)),VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE),VLOOKUP(19,'All Bus Models'!$A$5:$C$5000,2,FALSE)) =IF(ISNA(VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)),VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE),VLOOKUP(A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I put in 19 the formula is ok but if i put in a566 it returns
Thankyou very much for your time on my question.
Excellent Thankyou. "Miguel Zapico" wrote: Does this double coding happens normally? If so, I would recommend creating a new list, where you relate all the different codes that may appear on the data with the codes that are currently part of the 'All Bus Models'!$A$5:$C$5000. For example, if the value 19 is the one that works, you can create a list like this (let's say in worksheet "Aux") Column A Column B E19 19 19 19 ... (10000 rows, for example) And then change the formula to =VLOOKUP(VLOOKUP(--$A566,Aux!$A$1:$B10000,2,FALSE),'All Bus Models'!$A$5:$C$5000,2,FALSE) Would this work in your case? You can even try a mixed approach with: =IF(ISNA(VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)),VLOOKUP(VLOOKUP(--$A566,Aux!$A$1:$B500,2,FALSE),'All Bus Models'!$A$5:$C$5000,2,FALSE),VLOOKUP((--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)) Miguel. Miguel "Steved" wrote: Hello Miguel I see what you mean simple put i have a product number called 19 or E19 meaning one City has labelled it 19 the other city has labelled it E19 so I am wanting an eiher or formula which I have, if I put in 19 in the formula it works as it should but if I put in A Cell reference it won't, I just need to develop the formula so it will pick the cell in this case A566. Cheers. "Miguel Zapico" wrote: I am not sure of what the question is, but in both formulae you first check if a value is #N/A, and if it is, you use the same formula (that will show #N/A) as the result. You may try a formula like: =IF(ISNA(VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)),"Value not found",VLOOKUP((--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)) With this, it will show a message if the VLOOKUP is not successful, and return the value if it is. Hope this helps, Miguel. "Steved" wrote: Hello from Steved Below I have 2 formulas the first works the second does not Ok my objective is that on A566 I have E19 and on All Bus Models I have 19, What do I need to do please if I put in A566 it will pick up the value from All Bus Models. Thankyou. =IF(ISNA(VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)),VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE),VLOOKUP(19,'All Bus Models'!$A$5:$C$5000,2,FALSE)) =IF(ISNA(VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)),VLOOKUP(--$A566,'All Bus Models'!$A$5:$C$5000,2,FALSE),VLOOKUP(A566,'All Bus Models'!$A$5:$C$5000,2,FALSE)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| FORMULA RETURNS #VALUE WHEN PRESCEDENT WORKBOOK CLOSED | Excel Discussion (Misc queries) | |||
| Formula to sum every 4th cell returns #DIV/0! error in some column | Excel Worksheet Functions | |||
| IF Function returns result of formula | Excel Discussion (Misc queries) | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
