Using Find & Replace to add parameter to VLOOKUP formula

[yabrahamson]

Hi guys,

I have a spreadsheet with hundreds of VLOOKUP formulas that were inputted by a previous user. The formulas are correct except that they were all created with the VLOOKUP default settings that leave out the optional final parameter for Range Control and now need to be modified to include the FALSE parameter at the end that forces Excel to search for an exact match.

An example of the formula: =VLOOKUP($A$1,Students,79)

What it needs to become: =VLOOKUP($A$1,Students,79,FALSE) or =VLOOKUP($A$1,Students,79,0)

I have tried using Find: ) & Replace with: ,0) to find all ) characters and replace them with ,0) or ,FALSE), but this fails with the error "You've entered two many arguments for this function". All of these formulas are in the same 7 columns and no other formulas are used in those columns, so the Find & Replace will only find VLOOKUP formulas that need to be replaced as above. It seems that Excel thinks that I'm putting two values in the Replace with field separated by the comma. The interesting thing is sometimes I can make changes using the Replace option but the Replace All option consistently fails.

Any help would be greatly appreciated.

 

[steve the fish]

Hi. Then that means there must be other formulas in there that would fail. The situation you describe and the replace all option would work. So try doing it in sections to narrow down where it must be.

 

[steve the fish]

Heres one scenario that would cause it to fail and it may well be your problem...

Lets say you fix one VLOOKUP formula. If you then do your replace on that formula you would be:

=VLOOKUP($A$1,Students,79) The incorrect formula

=VLOOKUP($A$1,Students,79,0) The correct formula

=VLOOKUP($A$1,Students,79,0,0) This then fails!! Cant fix one then run the replace.

 

[yabrahamson]

Thank you for your reply.

I limited the find all to a small set of formulas and checked that all of them would work correctly. The Replace All still fails, however if I individually click on each entry in the find all list and click Replace, it works for every single one of them.

Any ideas?