VLOOKUP returning wrong value

Hey all,

I am trying to use the Vlookup function to return State name based on the corresponding state code.

I have the list of State along with the Codes in adjacent sheet, arranged in ascending order. I am using the Vlookup function to list the State name based on the State code.

Vlookup function: Vlookup(A4,Sheet2.A2:B51,1)

This function returns me a wrong value. It gives "Arkansas" for the state code "AZ", when it should be Arkansas.

Also, if I add "FALSE" as a 4th argument, it returns "#N/A".

I am attaching the file for your reference.

forum.ozgrid.com/index.php?attachment/54700/

Re: VLOOKUP returning wrong value

Did you mean D2 of sheet1? If so, then its returning #NAME? and Err:501 for either option.

Re: VLOOKUP returning wrong value

Thanks!! But I am still confused as to why VLOOKUP won't work in this case. That would make things so much simpler here.

Re: VLOOKUP returning wrong value

Thanks a lot!! That worked... Had to make the key column of the lookup table as the first one.

[COLOR="#0000CD"]MODERATOR EDIT[/COLOR]
The Excel Help on the VLOOKUP function is clear about the lookup column being the left-most column in the datable.
All columns must be to right of the lookup column

Re: VLOOKUP returning wrong value

Thanks for the link. This function sounds great. And I think I have another requirement where I assume I would be able to apply it. Can you please let me know how this can fit in this excel?

The City and State code will be the key fields. The State and country field will be filled based on State Code value. The value for City and State code will be entered manually, based on both these fields, state and postal code must be filled. Since there be be many cities inside US with same name in different state, I want the City as well as State code to be taken into account to fill out the State and Postal Code.

Let me know if INDEX AND MATCH can be useful in this case.

I forum.ozgrid.com/index.php?attachment/54711/

Quote from Fotis1991;672225

Take a look to this.

http://exceluser.com/blog/1107…-or-hlookup-in-excel.html

Re: VLOOKUP returning wrong value

Trying to create a database of Clients. Ignore the initial columns here plz.

Just the City, State, State code, Country and Zip Code are the ones that need to be focused on.

Re: VLOOKUP returning wrong value

Ok, lets consider 2 scenarios:

1. Ideally, I would enter City alone, and the State code, State, Postal Code and Country would be automatically filled (VLOOKUP function) - Figured out how to work, but can't use this one, since there many more than one city with the same name in US.

2. So, I would have to enter City and State Code, which have to be matched (with ref to Sheet 4). What do I do in this scenario when I want to make sure that the vlookup values are entered in State, Postal Code and Country, based on the City and its corresponding State code.

Am I clear now? Have a look at this excel sheet here.

Scenario 1 is depicted in Rows 1,2.
Scenario 2 is shown in row 3, where I manually entered the city "virginia" and State code "IL",. The State field changed to "Illinois" with respect to "IL", but the zip code obtains its value from City field remains the same as previous row (corresponding to the value from previous search "MN").

I want to use Scenario 2 for my use. But how do I do this taking both City and State Code into account?

forum.ozgrid.com/index.php?attachment/54717/

Re: VLOOKUP returning wrong value

Thanks a ton guys!!! That really helped... Appreciate your patience..