Lookup Single Value In Cells With 2 Values

  • Hello,


    I have following sample list of data:
    PostalCodes Country Terminal
    0000 - 1069 BE Grimb
    1100 - 1179 BE Grimb
    1070 - 1099 BE Tern
    1020 - 1229 AT LZN


    I now need to determine the Terminal for postalcode 1086 in BE
    but since the postal code that I need to find is within a range
    (1070-1099) that is within a single cell, and there is a second
    argument (BE) in the next column, my novice knowledge of VLOOKUP()
    isn't sufficient to come to a result


    Anyone any suggestions?


    thank you!

  • Re: lookup value within range in single cell


    Probably your best bet is to use the Data->Text to Columns... wizard to convert the single cells into multiple columns.


    Failing that, the following seemed to work:


    =MID(OFFSET($A$1,MATCH("1086",$A$2:$A$5,1),0),13,2)


    MATCH(value,array,1) works only if the array is sorted in ascending order, so be warned. HTH.

  • Re: lookup value within range in single cell


    If your data is in seperate columns, you could use
    =VLOOKUP(1080,A1:d4,3,TRUE)
    and it will return the row as close to or equal to what you're looking up.
    You can replace the hard-coded "1080" with a cell reference, and change A1:D4 to whatever your actual table range is. The key is the TRUE part. You must make sure that your data is sorted by the column that your key is in. In this case column A. This is also assuming that 1080 can only appear once in a range, which zip codes should be.


    Otherwise, you'll need to parse it like Mark said.

  • Re: Lookup Single Value In Cells With 2 Values


    Tks Kris for your (INDEX) formula and it works for the sample provided.
    I should have been more careful;
    for instance, in country FR or DE, the postal codes have more digits each
    e.g.
    PostcalCodes Country Terminal
    0000 - 1069 BE Grimb
    1100 - 1179 BE Grimb
    1070 - 1099 BE Tern
    1020 - 1229 AT LZN
    38000 - 38400 FR GNB
    25961 - 25990 DE KF
    401000 - 401300 RO Timi


    and one country (UK) even has alpha's included:


    AB0 - AB9ZZZ GB HDC
    BL78 - BL78ZZZ GB Colney



    It would already be great if the formula could take into account the lengths (4, 5, 6 digits) of the postal code
    I guess the UK postcode is too complex and I will have to validate manually, unless....



    tks for your great help
    Paul

  • Re: Lookup Single Value In Cells With 2 Values


    Well, for the numeric (or post code part), you could change the "4" in the first section to

    Code
    FIND(" - ", A1, 1)-1


    since you always have a " - " in between the two values.


    Looking for the country code will be a bit tougher though since there's no easy way to get to it - at least none that I can see immediated - without having it separated into multiple columns.

  • Re: Lookup Single Value In Cells With 2 Values


    Paul,


    Define Mx


    =TRIM(MID(Sheet1!$A2,FIND("-",Sheet1!$A2)+1,50))


    Define Ref


    =ROW(INDEX(Sheet1!$A:$A,1):INDEX(Sheet1!$A:$A,LEN(REPLACE(Mx,1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},Mx&1234567890))-1,""))))


    To extract Min Code no....


    in D2 and copied down,


    =LOOKUP(9.99999E+307,--MID(REPLACE(A2,1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A2&1234567890))-1,""),1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(REPLACE(A2,1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A2&1234567890))-1,""))))))


    For max Code No....


    InE2 and copied down,


    =LOOKUP(9.99999E+307,--MID(REPLACE(Mx,1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A2&1234567890))-1,""),1,Ref))


    In I2,


    =INDEX($C$2:$C$10,SUMPRODUCT(--(G2>=$D$2:$D$10),--(G2<=$E$2:$E$10),--($B$2:$B$10=H2),ROW($C$2:$C$10))-1)


    where G2 houses postal code and H2 houses country.


    See the attachment.


    HTH

  • Re: Lookup Single Value In Cells With 2 Values


    Maybe...


    1) Separate the postal codes into two columns


    2) Sort the table first by Country, then by the first column of Postal Codes


    3) Let F2 contain 1086, and G2 contain BE


    4) Then, try the following formula...


    =VLOOKUP(F2,INDEX(A2:A10,MATCH(G2,C2:C10,0)):INDEX(D2:D10,MATCH(G2,C2:C10)),4)


    Sample file attached...


    Hope this helps!

  • Re: Lookup Single Value In Cells With 2 Values


    Wow, overwhelming
    You gave me lots of good code to bring me towards a manageable solution.
    Very thankful to Krishnakumar et. al. for these contributions


    thank you!
    Paul

Participate now!

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