Question: Why is my vlookup function returning #REF!

By | February 5, 2015

The Problem

The WhereToSearch part of your vlookup formula is being told to look in a place that it can’t find.

The Solution

It could be that a cell that was previously being referenced is no longer there (has been deleted) or was never there. For example the formula might be looking in column 4 when you’ve only given it a 3 column table to look in

The formula below would return a #REF! and the 4 would need to be changed to 3 or 2.

=vlookup(“elucidate”, A:C, 4, false)

If you’re typing worksheet names in manually to the formula and it doesn’t match exactly the intended worksheet’s name, you’ll get a #REF!

Lastly, if the worksheet where the WhereToSearch part of your formula is referring to has been deleted since you created the formula… same again #REF!

Leave a Reply

Your email address will not be published. Required fields are marked *