I have a vlookup table to automatically input the population of a county when
I type in the county name. I am looking for a formula that will allow me to
crosscheck the spelling.

My vlookup table is in Column A and contains the name of the counties in
alphabetical order. Column B contains the population for each county.

Due to some fairly odd county names, I am concerned that the input will be
typed incorrectly. Therefore, I am looking for a formula to reference the
typed spelling against the accurate spelling in Column A. I would like the
cell calculation to respond, yes or no, true or false...whatever. I would
need this formula for each typed county.

I hope that is clear enough. If not, please ask.

Thanks!
Chersie

If VLOOKUP is set to find an exact match (fourth argument is either false or
0) then it should return #N/A if someone types the county name in wrong. Of
course, if they type it in wrong, but it matches the name of another county
in your table, I can't think of any way to prevent that.



"Chersie" wrote:

I have a vlookup table to automatically input the population of a county when
I type in the county name. I am looking for a formula that will allow me to
crosscheck the spelling.

My vlookup table is in Column A and contains the name of the counties in
alphabetical order. Column B contains the population for each county.

Due to some fairly odd county names, I am concerned that the input will be
typed incorrectly. Therefore, I am looking for a formula to reference the
typed spelling against the accurate spelling in Column A. I would like the
cell calculation to respond, yes or no, true or false...whatever. I would
need this formula for each typed county.

I hope that is clear enough. If not, please ask.

Thanks!
Chersie

Hi JMB,

Thank you so much for the suggestion/information. I actually figured it
out. My formula is:

=IF(VLOOKUP(H12,$A:$A,1)=H12,"Y","N")))

This verified the spelling, which was very cool. I actually had misspelled
two fo the counties.

What I have found when using VLookup is that if it is not spelled correctly,
it will return the value of the cell nearest the incorrect spelling. For
instance, three of the counties are Decatur, Dekalb, and Delaware. If I
input Dekall, it returns the population for Dekalb as it is the closest prior
input. But, if I input Dekala, it returns the population for Decatur.

So, in my spreadsheet, I would never get an error #N/A but the wrong
information. That is why it was so important for me to find a crosscheck.

Thanks again,
Chersie

"JMB" wrote:

If VLOOKUP is set to find an exact match (fourth argument is either false or
0) then it should return #N/A if someone types the county name in wrong. Of
course, if they type it in wrong, but it matches the name of another county
in your table, I can't think of any way to prevent that.



"Chersie" wrote:

I have a vlookup table to automatically input the population of a county when
I type in the county name. I am looking for a formula that will allow me to
crosscheck the spelling.

My vlookup table is in Column A and contains the name of the counties in
alphabetical order. Column B contains the population for each county.

Due to some fairly odd county names, I am concerned that the input will be
typed incorrectly. Therefore, I am looking for a formula to reference the
typed spelling against the accurate spelling in Column A. I would like the
cell calculation to respond, yes or no, true or false...whatever. I would
need this formula for each typed county.

I hope that is clear enough. If not, please ask.

Thanks!
Chersie

That is because you are not using the optional fourth argument. When
omitted, it defaults to TRUE, which will find an approximate match. When set
to FALSE it will look for an exact match and return #N/A when it is not found.

"Chersie" wrote:

Hi JMB,

Thank you so much for the suggestion/information. I actually figured it
out. My formula is:

=IF(VLOOKUP(H12,$A:$A,1)=H12,"Y","N")))

This verified the spelling, which was very cool. I actually had misspelled
two fo the counties.

What I have found when using VLookup is that if it is not spelled correctly,
it will return the value of the cell nearest the incorrect spelling. For
instance, three of the counties are Decatur, Dekalb, and Delaware. If I
input Dekall, it returns the population for Dekalb as it is the closest prior
input. But, if I input Dekala, it returns the population for Decatur.

So, in my spreadsheet, I would never get an error #N/A but the wrong
information. That is why it was so important for me to find a crosscheck.

Thanks again,
Chersie

"JMB" wrote:

If VLOOKUP is set to find an exact match (fourth argument is either false or
0) then it should return #N/A if someone types the county name in wrong. Of
course, if they type it in wrong, but it matches the name of another county
in your table, I can't think of any way to prevent that.



"Chersie" wrote:

I have a vlookup table to automatically input the population of a county when
I type in the county name. I am looking for a formula that will allow me to
crosscheck the spelling.

My vlookup table is in Column A and contains the name of the counties in
alphabetical order. Column B contains the population for each county.

Due to some fairly odd county names, I am concerned that the input will be
typed incorrectly. Therefore, I am looking for a formula to reference the
typed spelling against the accurate spelling in Column A. I would like the
cell calculation to respond, yes or no, true or false...whatever. I would
need this formula for each typed county.

I hope that is clear enough. If not, please ask.

Thanks!
Chersie

Hi JMB,

Yes, you are correct. I figured that out after I responded. I have never
used the fourth argument, but now I see how useful that can be.

Thanks again,
Chersie

"JMB" wrote:

That is because you are not using the optional fourth argument. When
omitted, it defaults to TRUE, which will find an approximate match. When set
to FALSE it will look for an exact match and return #N/A when it is not found.

"Chersie" wrote:

Hi JMB,

Thank you so much for the suggestion/information. I actually figured it
out. My formula is:

=IF(VLOOKUP(H12,$A:$A,1)=H12,"Y","N")))

This verified the spelling, which was very cool. I actually had misspelled
two fo the counties.

What I have found when using VLookup is that if it is not spelled correctly,
it will return the value of the cell nearest the incorrect spelling. For
instance, three of the counties are Decatur, Dekalb, and Delaware. If I
input Dekall, it returns the population for Dekalb as it is the closest prior
input. But, if I input Dekala, it returns the population for Decatur.

So, in my spreadsheet, I would never get an error #N/A but the wrong
information. That is why it was so important for me to find a crosscheck.

Thanks again,
Chersie

"JMB" wrote:

If VLOOKUP is set to find an exact match (fourth argument is either false or
0) then it should return #N/A if someone types the county name in wrong. Of
course, if they type it in wrong, but it matches the name of another county
in your table, I can't think of any way to prevent that.



"Chersie" wrote:

I have a vlookup table to automatically input the population of a county when
I type in the county name. I am looking for a formula that will allow me to
crosscheck the spelling.

My vlookup table is in Column A and contains the name of the counties in
alphabetical order. Column B contains the population for each county.

Due to some fairly odd county names, I am concerned that the input will be
typed incorrectly. Therefore, I am looking for a formula to reference the
typed spelling against the accurate spelling in Column A. I would like the
cell calculation to respond, yes or no, true or false...whatever. I would
need this formula for each typed county.

I hope that is clear enough. If not, please ask.

Thanks!
Chersie

You're most welcome.


"Chersie" wrote:

Hi JMB,

Yes, you are correct. I figured that out after I responded. I have never
used the fourth argument, but now I see how useful that can be.

Thanks again,
Chersie

"JMB" wrote:

That is because you are not using the optional fourth argument. When
omitted, it defaults to TRUE, which will find an approximate match. When set
to FALSE it will look for an exact match and return #N/A when it is not found.

"Chersie" wrote:

Hi JMB,

Thank you so much for the suggestion/information. I actually figured it
out. My formula is:

=IF(VLOOKUP(H12,$A:$A,1)=H12,"Y","N")))

This verified the spelling, which was very cool. I actually had misspelled
two fo the counties.

What I have found when using VLookup is that if it is not spelled correctly,
it will return the value of the cell nearest the incorrect spelling. For
instance, three of the counties are Decatur, Dekalb, and Delaware. If I
input Dekall, it returns the population for Dekalb as it is the closest prior
input. But, if I input Dekala, it returns the population for Decatur.

So, in my spreadsheet, I would never get an error #N/A but the wrong
information. That is why it was so important for me to find a crosscheck.

Thanks again,
Chersie

"JMB" wrote:

If VLOOKUP is set to find an exact match (fourth argument is either false or
0) then it should return #N/A if someone types the county name in wrong. Of
course, if they type it in wrong, but it matches the name of another county
in your table, I can't think of any way to prevent that.



"Chersie" wrote:

I have a vlookup table to automatically input the population of a county when
I type in the county name. I am looking for a formula that will allow me to
crosscheck the spelling.

My vlookup table is in Column A and contains the name of the counties in
alphabetical order. Column B contains the population for each county.

Due to some fairly odd county names, I am concerned that the input will be
typed incorrectly. Therefore, I am looking for a formula to reference the
typed spelling against the accurate spelling in Column A. I would like the
cell calculation to respond, yes or no, true or false...whatever. I would
need this formula for each typed county.

I hope that is clear enough. If not, please ask.

Thanks!
Chersie