Forum Discussion

majidsiddique52075's avatar
majidsiddique52075
Copper Contributor
May 05, 2019

Replace item AUTO if not match in invoice No

Hi All,

i am working on a sheet on two set of data bases. first is main data and second is invoice data. both are extract from different system software for analysis. main data is perfect and compare into invoice data and check all items are OK or not OK. similar i have a long invoices data where i have to compare with main data. first check invoice then match all item if there is presidency on there then replace to each other. you can see my attached file. 

i am using formula but failed. 

=IF(MATCH(A3,$F$3:$F$21,0)>0,A3,REPLACE(A3,1,11,F3))

 

Thanks.

Formulas and Functions

11 Replies

  • Tracy7212's avatar
    Tracy7212
    Tin Contributor
    Oct 07, 2026

    This might be related to your question: I want a way to flag a cell in Column B if the content does not exactly match the content on the same row in Column A.  My spreadsheet has hundreds of rows of data, and just a few mismatches, and I'd like an easy way to find them.  Seems like Conditional Formatting would offer an option, but I don't see one.  Is there a way to do this?

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 06, 2019

    majidsiddique52075 , you can't replace the value in another cell without VBA programming, formula returns the result of the calculations into the cell where this formula is.

     

    As for formula, MATCH returns #N/A error if match is not found, with that error entire formula also returns the same error. You may wrap the MATCH with IFNA or IFERROR to handle such situation.

     

    REPLACE(A19,1,11,F19) takes text from A19, replaces it's first eleven characters with the text from F19 and returns result into the cell where this formula is.