# VLOOKUP returning #N/A when dragging formula down

#### abeck1095

##### New Member
Hi, I am having the following issue with my VLOOKUP formula.

My formula works with the first cell and gives me a correct return value.
=VLOOKUP(C3,'Gas Data'!\$C\$3:\$F\$840,4,FALSE)

When the formula is dragged down I get the following:-
=VLOOKUP(C4,'Gas Data'!\$C\$3:\$F\$840,4,FALSE)

This formula in theory should be correct, as the lookup value I am trying to match is column C and the return value in column F of the separate tab.
However, all I get is #N/A.

Any help would be much appreciated as the match lookup value is correct and I can find it manually in 'Gas Data'

Thanks

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Two things to check:

1. Is your calculation method set to "Automatic?

2. Verify that your values really match for one returning an error (sometimes they look like they do, but they really don't - something as simple as an extra space can cause them NOT to match).
Here is how you do that.
Let's say that C4 is returning the #N/A error, and when you look at your "Gas Data" sheet, you see it should match cell C57.
Then in any blank cell on that main sheet, enter this formula:
Excel Formula:
``=C4='Gas Data'!C57``
If that formula returns FALSE, then despite what you think, those values really don't match.
Check for extra spaces, decimals, etc.

What sort of value do you have in C3, C4 etc?

Two things to check:

1. Is your calculation method set to "Automatic?

2. Verify that your values really match for one returning an error (sometimes they look like they do, but they really don't - something as simple as an extra space can cause them NOT to match).
Here is how you do that.
Let's say that C4 is returning the #N/A error, and when you look at your "Gas Data" sheet, you see it should match cell C57.
Then in any blank cell on that main sheet, enter this formula:
Excel Formula:
``=C4='Gas Data'!C57``
If that formula returns FALSE, then despite what you think, those values really don't match.
Check for extra spaces, decimals, etc.
Hi - Thanks for the quick reply.

I tried =C4='Gas Data'!C452 (which is the cell matching with C4 on my spread sheet and you're correct it has returned false)

However, the value in both Cells is a simple 5 digit number which I have expanded and checked does not have any hidden spaces, decimals etc.

I have also tried calculation method and it is set to Automatic (I noticed this was mentioned on a previous thread so had already confirmed this.

For your information - Cell C4 is 71199 and Cell C452 in 'Gas Data' is also 71199

Not sure what I can do?

OK, I am guessing that one of your values is entered as a number, and the other is a number entered as text.
VLOOKUP only works when matching on similar data types. You cannot match "numbers to text".
So you may need to fix one side or the other, so you are comparing "numbers to numbers" or "text to text".

You can use the ISNUMBER function to check to see if an entry is entered as a number or as text, i.e.
Excel Formula:
``=ISNUMBER(C4)``
and
Excel Formula:
``=ISNUMBER('Gas Data'!C452)``

Two things to check:

1. Is your calculation method set to "Automatic?

2. Verify that your values really match for one returning an error (sometimes they look like they do, but they really don't - something as simple as an extra space can cause them NOT to match).
Here is how you do that.
Let's say that C4 is returning the #N/A error, and when you look at your "Gas Data" sheet, you see it should match cell C57.
Then in any blank cell on that main sheet, enter this formula:
Excel Formula:
``=C4='Gas Data'!C57``
If that formula returns FALSE, then despite what you think, those values really don't match.
Check for extra spaces, decimals, etc

OK, I am guessing that one of your values is entered as a number, and the other is a number entered as text.
VLOOKUP only works when matching on similar data types. You cannot match "numbers to text".
So you may need to fix one side or the other, so you are comparing "numbers to numbers" or "text to text".

You can use the ISNUMBER function to check to see if an entry is entered as a number or as text, i.e.
Excel Formula:
``=ISNUMBER(C4)``
and
Excel Formula:
``=ISNUMBER('Gas Data'!C452)``
Many thanks, this was the issue - Numbers stored as text on one tab and not the other.
Is this a background process? as I have all the cells set to number.

Thanks for the help.

You can convert the "Numbers Stored as Text" using =NUMBERVALUE('number stored as text'). Then take those values, and Paste As Values' over the original data.

You can also use text to columns on the data to convert them to numbers. No formulae needed.

Replies
5
Views
38
Replies
9
Views
150
Replies
11
Views
199
Replies
4
Views
188
Replies
8
Views
48

1,206,971
Messages
6,075,925
Members
446,170
Latest member
zzzz02

### 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.

### Which adblocker are you using?

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

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