VLookup Question

[VQCHEESE]

Here is my current formula.
=IFERROR(VLOOKUP(K2,USCountyLookup!A1:E40365,5,FALSE),"NoMatch")

I get a nomatch in my entire column, unless i go into column K2 and hit enter and then I get Lookup Im looking for.

does this make sense? I have used this in other spreadsheets and its worked fine.

Any Ideas? Thanks

 

[hajiali]

can you give more detail on what sheets you have and where you have this formula. are you dragging the formula down the column or right across the rows?

 

[ShuStar]

Possibly something to do with calculate sheet... check if turned on or if you need to hit F9 for the data to get pulled...

 

[VQCHEESE]

I have a sheet called USCountyLookup. IT lists all the US zipcodes and the Countys to go with the zipcodes. ZipCodes are in Column1. The County Name is in column E.

On my sheet called "data"I have column K that has zipcodes that im using to lookup on the USCountyZIp Sheet which will give me the County Name.
Using my formula i posted above, i get "NoMatch". When i go into a field in K(example K2) and hit enter on the field Then i get the match that im looking for.

 

[ShuStar]

Hmmm wierd, not sure. It could be calculate sheet, formatting issue or something wrong with the workbook and creating from afresh may resolve it. Also vlookups could be fiddly in the sense it can only pull data from left to right (or vice versa, i forget which one).

Try an index match instead of vlookup. Logic as below:

=index ("the column/range of the value you want sits", Match( "cell value you want to find", "the column/range where this is", 0))

If I've read yours correctly, your formula should look something like:
=index(countylookup columnO, match(k2, countrylookupA1, 0))

If this dont work, check formatting or starting afresh from a new sheet.

Shustarr

 

[Fluff]

Sounds like column K is text & not numbers.
Try converting it to numbers using the Text to columns feature on the data tab.

 

[VQCHEESE]

Sounds like column K is text & not numbers.
Try converting it to numbers using the Text to columns feature on the data tab.

i think that might be my issue.
Whats syntax for that conversion in excel formula

 

[Fluff]

No need for a formula, just select col K, then on data tab select text to columns > Delimited > Next > clear all check boxes > Finish.

 

[VQCHEESE]

I think i got it working. That was my issue. My problem is i have this data linked from a SQL database to refresh on open. So i made a 2nd column for my zipcode and used =[@zipcode]*1.

 

[Fluff]

Glad you sorted it & thanks for the feedback.