vlookup function

[muskan27]

Q. Find the city name based on the city codes .....
I used the Vlookup function

The final answer displayed : Providence, RI


The answer should be - Providence

Attaching a sample data below...

How to proceed ?
Please help

[The_Snook]

Hi Muskan27,

Pop this in cell H6 and copy it down:

=LEFT(VLOOKUP(G6,$A$2:$B$16,2,0),FIND(",",VLOOKUP(G6,$A$2:$B$16,2,0))-1)

NB - MKE doesn't find a match as there's a trailing space in cell G10.

Regards,

Snook

[Arkadi]

In H6, then copy down (Note that MKE wont work because you have a space after it)

Please Login or Register  to view this content.

or if there isn't always going to be a comma:

Please Login or Register  to view this content.

[muskan27]

Thank you for helping me out ... it worked perfectly

[Arkadi]

Glad to hear it Please remember to mark the thread as solved? Thanks!

[muskan27]

In H6, then copy down (Note that MKE wont work because you have a space after it)

Please Login or Register  to view this content.

or if there isn't always going to be a comma:

Please Login or Register  to view this content.

I have a doubt
In case the column positions are interchanged in the data set i.e., origin city name in the first column and origin city code in the second.. How will we answer using INDEX and/or MATCH functions ?
I have made modifications in the sample data ... new data set attached below

[The_Snook]

Hi muskan27,

In that instance you can use this:

=LEFT(INDEX($A$2:$A$16,MATCH(F6,$B$2:$B$16,0)),FIND(",",INDEX($A$2:$A$16,MATCH(F6,$B$2:$B$16,0)))-1)

Regards,

Snook

[Arkadi]

Please Login or Register  to view this content.

[muskan27]

Thanks ! The_Snook