I put in 19 the formula is ok but if i put in a566 it returns #N/A

[Guest]

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))

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

Thankyou very much for your time on my question.

Excellent

Thankyou.