Using Find & Replace to add parameter to VLOOKUP formula

yabrahamson

New Member
Joined
Jul 27, 2017
Messages
2
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top