VLOOKUP returning #N/A when dragging formula down

[abeck1095]

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

 

[Joe4]

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:

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

 

[Fluff]

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

 

[abeck1095]

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:

=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?

 

[Joe4]

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.

=ISNUMBER(C4)

and

=ISNUMBER('Gas Data'!C452)

 

[abeck1095]

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:

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

=ISNUMBER(C4)

and

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

 

[Grasor]

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.

 

[Fluff]

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