nested formulas do not evaluate when cell data is manipulated

[AlexDz]

I have run into this problem a few times, possibly a problem with Vlookup or formulas.

A2 = XX-XXXXX

=IFERROR(VLOOKUP(A2,Sheet2'!$B:$E,4,FALSE),VLOOKUP(SUBSTITUTE(A2,"-",""),Sheet2'!$B:$E,4,FALSE))

Trying to search for a value without a hyphen in the event it doesn't work with the hyphen.
When I try and nest a substitute formula or other such formula it would evaluate with F9 properly
but then the Vlookup would not evaluate the reference to the changed value of A2.... "XXXXXX"

Result N/A

note A2 typed in with out the hyphen does work in this vlookup.

[rorya]

Do the Xs represent numbers? If so, you probably need to convert the result of the SUBSTITUTE to a number like this:

=IFERROR(VLOOKUP(A2,Sheet2'!$B:$E,4,FALSE),VLOOKUP(SUBSTITUTE(A2,"-","")+0,Sheet2'!$B:$E,4,FALSE))

[davsth]

The obvious is you are missing/including '

=IFERROR(VLOOKUP(A2,Sheet2!$B:$E,4,FALSE),VLOOKUP(SUBSTITUTE(A2,"-",""),Sheet2!$B:$E,4,FALSE))

may work it does on my spreadsheet

however you really need to attach a sample with the error if that fails

[AlexDz]

That, worked thank you! Now why does that work?

[rorya]

SUBSTITUTE returns a text value. If the numbers are stored as numbers in the lookup table, the formula won't find a match since "1" is not the same as 1 to VLOOKUP. Adding 0 will coerce it to a real number value.