VLOOKUP returning wrong value

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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


    In B2 and copy down try using INDEX & MATCH.


    =INDEX(Sheet2!$A$2:$A$100,MATCH(A2,Sheet2!$B$2:$B$100,0))


    =iferror(=INDEX(Sheet2!$A$2:$A$100,MATCH(A2,Sheet2!$B$2:$B$100,0)),"")

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

    Re: VLOOKUP returning wrong value


    Index & Match is the way to go. Put this in B2 of sheet 1 and copy down:


    =IFERROR(INDEX(Sheet2!$A$1:$B$51,MATCH(A2,Sheet2!$B$1:$B$51,0),1),"")


    Dan

    [SIZE=1]It's like asking a mechanic to fix your car, without actually taking your car to him. Post your code/file and you'll get much quicker and more accurate solutions to your problem.[/SIZE]

    Re: VLOOKUP returning wrong value


    ...................................

    Files

    0a42.xls 33.79 kB – 166 Downloads

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

    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


    Take a look to this.


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

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

    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


    Can you explain your goal using the columns? COLUMN a, COLUMN b...AND SO ON..PLS?

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

    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


    One more try...


    In which column do you want your result? Which column(s) must we use for Match?


    Give us an example in which VLookup(that you use) does not gives the correct results.

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

    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


    I think now it's clear.


    Use this ARRAY formula.


    =INDEX(Sheet4!$C$2:$C$100;MATCH(A2&B2;Sheet4!$A$2:$A$100&Sheet4!$B$2:$B$100;0))

    Files

    0a43.xls 33.28 kB – 168 Downloads

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login